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?
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.


regards, barry

Rob HensonFinance AnalystCommented:
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?
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.