[Solved] Automating income tax forms. "If" statements in writer or movable fields linked to cells in calc?

7 replies [Last post]
goedible
Offline
Last seen: 14 weeks 4 days ago
Title: ★★★★
Joined: 18 May 2012
Posts: 72
Hi. My government offers fillable .pdf files for my taxes but they are not automated. Who knows why? I need help automating the forms.

I’ve used writer to import an image of my tax form and created fields to input data and do calculations. I was able to do simple things like =Table24.A1-Table25.A1 or division or multiplication… no problem.

I can not find a way to create formulas like in calc with if statements like =if(A1<0,0,A1). I tried =if(Table24.A1-Table25.A1)<0,0,Table24.A1-Table25.A1) and this does not work.

Is there a way to create fields on top of my image in Calc and link them to the underlying spreadsheet cells or a way to do functions in writer?

For the purpose of illustration, I’ve attached my 2014 tax return for you to look at. See page 4, part 8, line 2. This needs to be an “if” statement. Here is the link

Thank you for your consideration.

*EDIT (Mod)Sexy Thread moved to Base forum for greater visibility to Base experts. Please disregard my erroneous comments below.

Facebook Jonathan Brian Francoeur Libre
oweng
oweng's picture
Offline
Last seen: 5 days 10 hours ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 3281
Writer formula / embed spreadsheet cell
The list of formulas available for insertion into a Writer table is a fairly limited subset of that available in Calc. Perhaps try embedding/linking a spreadsheet cell with the required conditional?
goedible
Offline
Last seen: 14 weeks 4 days ago
Title: ★★★★
Joined: 18 May 2012
Posts: 72
Writer formula / embed spreadsheet cell
Oweng: thank you for your suggestion.

I’ve tried however I don’t know how to do that. I found this discussion: https://forum.openoffice.org/en/forum/viewtopic.php?t=4724&p=21692 that deals with turning to base and using forms.

Have you embed a spreadsheet cell in writer? Is it possible?

Facebook Jonathan Brian Francoeur Libre
oweng
oweng's picture
Offline
Last seen: 5 days 10 hours ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 3281
DDE linking
Try the instructions here.
goedible
Offline
Last seen: 14 weeks 4 days ago
Title: ★★★★
Joined: 18 May 2012
Posts: 72
DDE linking does not allow for input to form.
I believe that we have to come up with a solution that allows the user to input values onto the writer doc (form) and have a spreadsheet do the underlying calculations.

It appears that DDE linking is a good solution for displaying in a writer doc the results of a cell in a calc doc but I do not see how it can be used to insert data to the calc. doc for the calculation.

With a DDE link, the user has to input values to a speadsheet before they show up on the writer doc.

For the purpose of illustration, I’ve attached my 2014 tax return for you to look at. See page 4, part 8, line 2. This needs to be an “if” statement. Here is the link

I will post this question on the base forum, perhaps base can perform functions with if statements?

Facebook Jonathan Brian Francoeur Libre
oweng
oweng's picture
Offline
Last seen: 5 days 10 hours ago
Title: ==Moderator==
Joined: 26 Jan 2012
Posts: 3281
sorry
I am so sorry. I seem to have completely missed the point about this being a form. I will move the thread to the Base forum and PM a few of our Base experts. Again, sorry.
goedible
Offline
Last seen: 14 weeks 4 days ago
Title: ★★★★
Joined: 18 May 2012
Posts: 72
I figured it out!
I created two new “hidden” fields to calculate the final result. I made them hidden by changing the font colour the same as the background. One holds the answer the math problem. The other uses the “G” operator and inputs a 1 or 0 based on if the problem was greater than or less than 0. The final result field multiplies the two new fields. If the result in the first field was less than 0 the result was 0.

Thank Oweng for sharing this link https://help.libreoffice.org/Writer/Formula

Here is the template http://templates.libreoffice.org/template-center/t2125-15e-canada-revenue-agency-statement-of-business-or-professional-activities-income-tax

Facebook Jonathan Brian Francoeur Libre
Johnbuchmiller
Offline
Last seen: 3 weeks 5 days ago
Title:
Joined: 3 May 2016
Posts: 1
I had seen the exclusion
I had seen the exclusion lists on line but had made a very basic error of assuming I was using the latest version because I had downloaded the latest Open Office. So this now obviously leads me to the next question but when i contact Chicago IRS Lawyers they guide me well.Just want to say thanks for their prompt response, appreciate it.
johnbuchmiller

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.