• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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?
0
AccountantsTech
Asked:
AccountantsTech
1 Solution
 
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 HensonIT & Database AssistantCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now