I have a table within a form that I want to add a calculation into row 3 with row 1 minus row 2 for each column. Is there a way for me to be able to do this?
There are two ways you could do this:
1. Take the third column out of your table and make them separate fields. You would have a table with two rows and two columns, and two separate fields for remaining years and remaining dates. Set up the calculation in remaining years as
= IF(INDEX(Table.Years,1)="",0,INDEX(Table.Years,1)) - IF(INDEX(Table.Years,2)="",0,INDEX(Table.Years,2))
And months as
= IF(INDEX(Table.Months,1)="",0,INDEX(Table.Months,1))
- IF(INDEX(Table.Months,2)="",0,INDEX(Table.Months,2))
Basically says take years from row 1 and subtract years from row 2 (with some handling in case they are blank).
If you format it so Years and Months are next to each other, you probably can get it close to look like the table you were initially after above.
2. Transpose the table so the rows are Years and Months, and the columns are Total, Credit and Remaining time. Since you can do calculations across the table, you can fill the remaining time with the calculation
=INDEX(Table_1.Total,row()) - INDEX(Table_1.Credit,row())
Just saying take the total and subtract the credit in each row. Both solutions only work for a fixed sized table.
Hi Bill
You cannot do it with Forms Calculations in the same field in a table as it creates a Circular Reference.