Debbie Sap
New Member
- Joined
- Oct 20, 2014
- Messages
- 14
Hi
I am trying to create a dynamic formula (not sure if this is the correct word where the total for Columns for Year 1, 2,3 are sum of all the months above provided there are figures in year 4.
For example, for the table, we would sum July - November for Year1,2,3,4. Then next month when a figure is entered in December it would sum July - December for all 4 columns.
An Example table:
[TABLE="width: 288"]
[TR]
[TD="width: 64, bgcolor: transparent"]Row 4[/TD]
[TD="width: 64, bgcolor: transparent"]Month[/TD]
[TD="width: 64, bgcolor: transparent"]Year 1[/TD]
[TD="width: 64, bgcolor: transparent"]Year 2[/TD]
[TD="width: 64, bgcolor: transparent"]Year 3 [/TD]
[TD="width: 64, bgcolor: transparent"]Year 4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 5[/TD]
[TD="bgcolor: yellow"]July[/TD]
[TD="bgcolor: yellow, align: right"]20[/TD]
[TD="bgcolor: yellow, align: right"]40[/TD]
[TD="bgcolor: yellow, align: right"]60[/TD]
[TD="bgcolor: yellow, align: right"]30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 6[/TD]
[TD="bgcolor: yellow"]Aug[/TD]
[TD="bgcolor: yellow, align: right"]70[/TD]
[TD="bgcolor: yellow, align: right"]90[/TD]
[TD="bgcolor: yellow, align: right"]70[/TD]
[TD="bgcolor: yellow, align: right"]40[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 7[/TD]
[TD="bgcolor: yellow"]Sept[/TD]
[TD="bgcolor: yellow, align: right"]90[/TD]
[TD="bgcolor: yellow, align: right"]10[/TD]
[TD="bgcolor: yellow, align: right"]15[/TD]
[TD="bgcolor: yellow, align: right"]44[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 8[/TD]
[TD="bgcolor: yellow"]Oct[/TD]
[TD="bgcolor: yellow, align: right"]12[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]18[/TD]
[TD="bgcolor: yellow, align: right"]44[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 9[/TD]
[TD="bgcolor: yellow"]Nov[/TD]
[TD="bgcolor: yellow, align: right"]18[/TD]
[TD="bgcolor: yellow, align: right"]19[/TD]
[TD="bgcolor: yellow, align: right"]30[/TD]
[TD="bgcolor: yellow, align: right"]11[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 10[/TD]
[TD="bgcolor: transparent"]Dec[/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]80[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 11[/TD]
[TD="bgcolor: transparent"]Jan[/TD]
[TD="bgcolor: transparent, align: right"]70[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]133[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 12[/TD]
[TD="bgcolor: transparent"]Feb[/TD]
[TD="bgcolor: transparent, align: right"]80[/TD]
[TD="bgcolor: transparent, align: right"]99[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 13[/TD]
[TD="bgcolor: transparent"]March [/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 14[/TD]
[TD="bgcolor: transparent"]Aprl[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]32[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 15[/TD]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent, align: right"]228[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]80[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 16[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent, align: right"]99[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]YTD[/TD]
[TD="bgcolor: transparent"]July - Nov[/TD]
[TD="bgcolor: transparent, align: right"]210[/TD]
[TD="bgcolor: transparent, align: right"]176[/TD]
[TD="bgcolor: transparent, align: right"]193[/TD]
[TD="bgcolor: transparent, align: right"]169[/TD]
[/TR]
[/TABLE]
Thanks for your help
I am trying to create a dynamic formula (not sure if this is the correct word where the total for Columns for Year 1, 2,3 are sum of all the months above provided there are figures in year 4.
For example, for the table, we would sum July - November for Year1,2,3,4. Then next month when a figure is entered in December it would sum July - December for all 4 columns.
An Example table:
[TABLE="width: 288"]
[TR]
[TD="width: 64, bgcolor: transparent"]Row 4[/TD]
[TD="width: 64, bgcolor: transparent"]Month[/TD]
[TD="width: 64, bgcolor: transparent"]Year 1[/TD]
[TD="width: 64, bgcolor: transparent"]Year 2[/TD]
[TD="width: 64, bgcolor: transparent"]Year 3 [/TD]
[TD="width: 64, bgcolor: transparent"]Year 4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 5[/TD]
[TD="bgcolor: yellow"]July[/TD]
[TD="bgcolor: yellow, align: right"]20[/TD]
[TD="bgcolor: yellow, align: right"]40[/TD]
[TD="bgcolor: yellow, align: right"]60[/TD]
[TD="bgcolor: yellow, align: right"]30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 6[/TD]
[TD="bgcolor: yellow"]Aug[/TD]
[TD="bgcolor: yellow, align: right"]70[/TD]
[TD="bgcolor: yellow, align: right"]90[/TD]
[TD="bgcolor: yellow, align: right"]70[/TD]
[TD="bgcolor: yellow, align: right"]40[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 7[/TD]
[TD="bgcolor: yellow"]Sept[/TD]
[TD="bgcolor: yellow, align: right"]90[/TD]
[TD="bgcolor: yellow, align: right"]10[/TD]
[TD="bgcolor: yellow, align: right"]15[/TD]
[TD="bgcolor: yellow, align: right"]44[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 8[/TD]
[TD="bgcolor: yellow"]Oct[/TD]
[TD="bgcolor: yellow, align: right"]12[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]18[/TD]
[TD="bgcolor: yellow, align: right"]44[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 9[/TD]
[TD="bgcolor: yellow"]Nov[/TD]
[TD="bgcolor: yellow, align: right"]18[/TD]
[TD="bgcolor: yellow, align: right"]19[/TD]
[TD="bgcolor: yellow, align: right"]30[/TD]
[TD="bgcolor: yellow, align: right"]11[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 10[/TD]
[TD="bgcolor: transparent"]Dec[/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]80[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 11[/TD]
[TD="bgcolor: transparent"]Jan[/TD]
[TD="bgcolor: transparent, align: right"]70[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]133[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 12[/TD]
[TD="bgcolor: transparent"]Feb[/TD]
[TD="bgcolor: transparent, align: right"]80[/TD]
[TD="bgcolor: transparent, align: right"]99[/TD]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 13[/TD]
[TD="bgcolor: transparent"]March [/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 14[/TD]
[TD="bgcolor: transparent"]Aprl[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]32[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 15[/TD]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent, align: right"]228[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]80[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Row 16[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent, align: right"]99[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]YTD[/TD]
[TD="bgcolor: transparent"]July - Nov[/TD]
[TD="bgcolor: transparent, align: right"]210[/TD]
[TD="bgcolor: transparent, align: right"]176[/TD]
[TD="bgcolor: transparent, align: right"]193[/TD]
[TD="bgcolor: transparent, align: right"]169[/TD]
[/TR]
[/TABLE]
Thanks for your help