Indirect function with table formula

sccole

New Member
Joined
Aug 16, 2011
Messages
11
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
ABCDEFGHIJKLMN
1Month to measureMar
2
3NameJan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec YTD Quota
4Name 1231,000254,100284,900252,000277,200310,800273,000300,300336,700294,000323,400362,600770,000
5Name 2231,000254,100284,900252,000277,200310,800273,000300,300336,700294,000323,400362,600770,000
6Name 3462,000508,200569,800504,000554,400621,600546,000600,600673,400588,000646,800725,2001,540,000
Sheet1
Cell Formulas
RangeFormula
N4:N6N4=SUM(Main[@[Jan ]:[Mar ]])
 
Last edited by a moderator:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Try...

=SUM([@Jan]:INDEX(Table1[[#Headers],[Jan]:[Dec]],MATCH($B$1,Table1[[#Headers],[Jan]:[Dec]],0)))
 
Upvote 0
Solution
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.
 
Upvote 0
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
ABCDEFGHIJKLMN
1MonthFeb
2
3NameJanFebMarAprMayJunJulAugSepOctNovDecAnnual
4Name1231,000254,100284,900252,000277,200310,800273,000300,300336,700294,000323,400362,600485,100
5Name2231,000254,100284,900252,000277,200310,800273,000300,300336,700294,000323,400362,600970,200
6Name3462,000508,200569,800504,000554,400621,600546,000600,600673,400588,000646,800725,2001,940,400
Sheet1
Cell Formulas
RangeFormula
N4:N6N4=SUM([@Jan]:INDEX(Table2[[#Headers],[Jan]:[Dec]],MATCH($B$1,Table2[[#Headers],[Jan]:[Dec]],0)))

Can you help? Thanks again
 
Last edited by a moderator:
Upvote 0
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
ABCDEFGHIJKLMN
1MonthFeb
2
3NameJanFebMarAprMayJunJulAugSepOctNovDecAnnual
4Name 1231,000254,100284,900252,000277,200310,800273,000300,300336,700294,000323,400362,600485100
5Name 2231,000254,100284,900252,000277,200310,800273,000300,300336,700294,000323,400362,600485100
6Name 3462,000508,200569,800504,000554,400621,600546,000600,600673,400588,000646,800725,200970200
Sheet1
Cell Formulas
RangeFormula
N4:N6N4=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.
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top