Use cell data as table name in formula

BobSullivan
3StarLounger
Posts: 220
Joined: 08 Jun 2010, 20:03
Location: Morgantown, PA

Use cell data as table name in formula

Post by BobSullivan »

I have monthly data in worksheets. Each worksheet contains one table, the table name is the month name. There's a January table, a February table and so forth.
On a summary page, I have the month names in column A. In column B I want to calculate the totals of a column called "Total" from each table.
So the worksheet would look like this:
Column A Column B
January =SUM(January[Total])
February =Sum(February[Total])

and so forth. What I would like to do is have the formula in Column B use the text in column A as the table name. That way, I could easily copy the formula down for all 12 months. How can I use the text in a cell as a table name in a formula?

Thanks!
Cordially,

Bob Sullivan
Elverson, PA

BobSullivan
3StarLounger
Posts: 220
Joined: 08 Jun 2010, 20:03
Location: Morgantown, PA

Re: Use cell data as table name in formula

Post by BobSullivan »

Found my answer. The way to do this is to create the following formula:
=SUM(INDIRECT(A1&"[Total]"))
The table column needs to be part of the indirect function.
Cordially,

Bob Sullivan
Elverson, PA

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Use cell data as table name in formula

Post by HansV »

Good for you!
Regards,
Hans