Excel Table formula

Hi, I am working on a report in excel that is doing a sumifs to a particular table.  I would like to use a variable to switch columns for the sum range so I can just change a cell and it would calculate.

Here is my formula:
=-SUMIFS(Input1[Division1],Input1[MapID],400,Input1[Month],CC504,Input1[Year],Current_Year)
This works just fine however, I would like to use a formula like this:
=-SUMIFS(Input1["A1"],Input1[MapID],400,Input1[Month],CC504,Input1[Year],Current_Year)
Then cell A1 would have "Division1"
That way all I would need to do is change cell A1  to "Division1" or "Division2" etc and I could use the same report for various division by simply changing the value in cell A1

I can't come up with the formula!  Anyone got a suggestion?
AccountantsTechAsked:
Who is Participating?
 
barry houdiniCommented:
I don't think you can put a cell reference directly into that sort of Table reference formula, even with INDIRECT function. I think you can use INDEX/MATCH to get the relevant column, though, i.e.

=-SUMIFS(INDEX(Input1[#Data],0,MATCH(A1,Input1[#Headers],0)),Input1[MapID],400,Input1[Month],CC504,Input1[Year],Current_Year)

regards, barry

0
 
Rob HensonFinance AnalystCommented:
How about having a pivot table on the original data and use the division as a page reference.

Cheers
Rob H
0
 
AccountantsTechAuthor Commented:
That formula worked great!  It is really great!  Now I would like to know if I could also put the table name in say cell A2 so I can specify the table.   I have four different tables all formatted the same. Actual Number, Budget Numbers.  Any suggestions on being able to do this?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.