Excel Table formula

Posted on 2011-05-06
Last Modified: 2012-05-11
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?
Question by:AccountantsTech
    LVL 50

    Accepted Solution

    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

    LVL 31

    Expert Comment

    by:Rob Henson
    How about having a pivot table on the original data and use the division as a page reference.

    Rob H

    Author Comment

    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?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now