• 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:
This works just fine however, I would like to use a formula like this:
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?
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.


regards, barry

Rob HensonIT & Database AssistantCommented:
How about having a pivot table on the original data and use the division as a page reference.

Rob H
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?

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