Indirect function with table formula

[sccole]

I am using the table function and structured reference for a formula on the far right marked in yellow, "YTD Quota". That formula is: =SUM(Main[@[Jan]:[Mar]]), and sums Jan, Feb and Mar (also in yellow) in the row "Name 1". I would like the second sum term in the formula "Mar" to be variable and to refer to a value outside the table. This way I could change Mar to Apr, May etc in the "Month to measure field" at the top of the table.

I've tried to use Indirect function in the formula (in place of "Mar")to achieve this, but I can't get the syntax right. Any ideas?

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Month to measure	Mar
2
3	Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	YTD Quota
4	Name 1	231,000	254,100	284,900	252,000	277,200	310,800	273,000	300,300	336,700	294,000	323,400	362,600	770,000
5	Name 2	231,000	254,100	284,900	252,000	277,200	310,800	273,000	300,300	336,700	294,000	323,400	362,600	770,000
6	Name 3	462,000	508,200	569,800	504,000	554,400	621,600	546,000	600,600	673,400	588,000	646,800	725,200	1,540,000
Sheet1

Cell Formulas
Range	Formula
N4:N6	N4	=SUM(Main[@[Jan ]:[Mar ]])

 

[Aladin Akyurek]

Try...

=SUM([@Jan]:INDEX(Table1[[#Headers],[Jan]:[Dec]],MATCH($B$1,Table1[[#Headers],[Jan]:[Dec]],0)))

 

[sccole]

Thanks so much and I think it should work but there is still a syntax error. I copied the formula exactly and I still receive the error. I will keep playing with it, but if you have any ideas please let me know. Thanks again.

 

[sccole]

Aladin,

NVM, it works! Thanks. Just had to change the name of the table. Thanks again.

 

[Aladin Akyurek]

Aladin,

NVM, it works! Thanks. Just had to change the name of the table. Thanks again.

That's great. Thanks for providing feedback.

 

[sccole]

Aladin, just one more thing if I might. I just noticed that the formula works for the first row but is incorrect for subsequent rows: it seems to add the total of the entire previous row to the calculation.

Formula (located in Annual column): =SUM([@Jan]:INDEX(Table2[[#Headers],[Jan]:[Dec]],MATCH($B$1,Table2[[#Headers],[Jan]:[Dec]],0)))

Result:

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Month	Feb
2
3	Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Annual
4	Name1	231,000	254,100	284,900	252,000	277,200	310,800	273,000	300,300	336,700	294,000	323,400	362,600	485,100
5	Name2	231,000	254,100	284,900	252,000	277,200	310,800	273,000	300,300	336,700	294,000	323,400	362,600	970,200
6	Name3	462,000	508,200	569,800	504,000	554,400	621,600	546,000	600,600	673,400	588,000	646,800	725,200	1,940,400
Sheet1

Cell Formulas
Range	Formula
N4:N6	N4	=SUM([@Jan]:INDEX(Table2[[#Headers],[Jan]:[Dec]],MATCH($B$1,Table2[[#Headers],[Jan]:[Dec]],0)))

Can you help? Thanks again

 

[Aladin Akyurek]

Aladin, just one more thing if I might. I just noticed that the formula works for the first row but is incorrect for subsequent rows: it seems to add the total of the entire previous row to the calculation.

Formula (located in Annual column): =SUM([@Jan]:INDEX(Table2[[#Headers],[Jan]:[Dec]],MATCH($B$1,Table2[[#Headers],[Jan]:[Dec]],0)))

Result:
...
Can you help? Thanks again

I don't understand why you change the figures and YTD Quota to Annual for such action can thwart communication...

See below:

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Month	Feb
2
3	Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Annual
4	Name 1	231,000	254,100	284,900	252,000	277,200	310,800	273,000	300,300	336,700	294,000	323,400	362,600	485100
5	Name 2	231,000	254,100	284,900	252,000	277,200	310,800	273,000	300,300	336,700	294,000	323,400	362,600	485100
6	Name 3	462,000	508,200	569,800	504,000	554,400	621,600	546,000	600,600	673,400	588,000	646,800	725,200	970200
Sheet1

Cell Formulas
Range	Formula
N4:N6	N4	=SUM([@Jan]:INDEX(Table2[@[Jan]:[Dec]],MATCH($B$1,Table2[[#Headers],[Jan]:[Dec]],0)))

The exhibit occupies A1:N7. The table covers the area in A4:N7.

The formula in N4 is:

=SUM([@Jan]:INDEX(Table2[@[Jan]:[Dec]],MATCH($B$2,Table2[[#Headers],[Jan]:[Dec]],0)))

which is copied down.

 

[sccole]

Aladin,

Thanks and sorry for the change. It all works.

 

[Aladin Akyurek]

Aladin,

Thanks and sorry for the change. It all works.

Happy to hear that. Thanks for providing feedback.