# Variable Table Reference Based on Cell Value

#### wryan_garner4

##### New Member
I have a workbook with thirteen sheets, named January through December, with the last sheet being named Totals. In each of the first twelve sheets, I have tables (with headers), named the same as the tab that they are on. Example, Tab named January with a table named January. All thirteen forms have the same formatting for tables, same number of columns and row, with the same header values. On sheets January to December, the table starts in A1. On the Totals sheet, the table starts in C1. I have a drop down validation list in cell A1 of the Totals sheets with a list of the other sheet names. I would like a formula that will pull a sum of the data from each respective table, based on the value in cell A1 from the validation list. There are no formulas on the first twelve sheets, but I am currently using the following formulas (which need to be fixed) on the Totals sheet.
Current Formulas:
Cell A4 =IFERROR(INDEX(December[[Source ]], MATCH(0,COUNTIF(A\$1:\$E3,December[[Source ]]), 0)),"")
Cell B4 =SUMIF(December[[Source ]],A4,December[Expected Amount])
Cell B5 =SUMIF(December[[Source ]],A4,December[Actual Amount])

So my question..... How do I use cell A1 value within a formula to get the information that I need? I have attached two photos for reference. The first is of sheet December, the second is of sheet Totals.

#### Attachments

• December.png
17.5 KB · Views: 17
• Totals.png
18 KB · Views: 18

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

##### Well-known Member
Hi Wryan_Garner4,

With your 365 you'll have a better option to retrieve the distinct source entries but with my Excel 2016 I'll use AGGREGATE.

I believe this does what you want.

wryan_garner4.xlsx
ABCD
1LocationSourceExpected AmountActual Amount
7
8
9
10
11
December

wryan_garner4.xlsx
ABC
1December
2
3SourceExpected AmountActual Amount
4Wikipedia\$ 1,000.00\$ 950.00
6
7
Totals
Cell Formulas
RangeFormula
A4:A7A4=IFERROR(INDEX(INDIRECT(\$A\$1&"["&A\$3&"]"),AGGREGATE(15,6,ROW(INDIRECT(\$A\$1&"["&A\$3&"]"))-1/(ISNA(MATCH(INDIRECT(\$A\$1&"["&A\$3&"]"),\$A\$3:\$A3,0))),1))&"","")
B4:C7B4=IF(\$A4="","",SUMIF(INDIRECT(\$A\$1&"["&\$A\$3&"]"),\$A4,INDIRECT(\$A\$1&"["&B\$3&"]")))

#### Fluff

##### MrExcel MVP, Moderator
Another option for xl365
+Fluff 1.xlsm
ABC
1December
2
3SourceExpected AmountActual Amount
4Wikipedia1000950
Main
Cell Formulas
RangeFormula
A4:A5A4=LET(tbl,INDIRECT(A1&"["&A3&"]"),UNIQUE(FILTER(tbl,tbl<>"")))
B4:C5B4=SUMIFS(INDIRECT(\$A\$1&"["&B\$3&"]"),INDIRECT(\$A\$1&"["&\$A\$3&"]"),\$A4#)
Dynamic array formulas.

#### wryan_garner4

##### New Member
Another option for xl365
+Fluff 1.xlsm
ABC
1December
2
3SourceExpected AmountActual Amount
4Wikipedia1000950
Main
Cell Formulas
RangeFormula
A4:A5A4=LET(tbl,INDIRECT(A1&"["&A3&"]"),UNIQUE(FILTER(tbl,tbl<>"")))
B4:C5B4=SUMIFS(INDIRECT(\$A\$1&"["&B\$3&"]"),INDIRECT(\$A\$1&"["&\$A\$3&"]"),\$A4#)
Dynamic array formulas.
Thank you. This has worked well for me. I am now trying to add a couple columns to the right that will pull that same Source list from all sheets, remove duplicates, then get a sum of all of the Expected Amounts and Actual Amounts. I have tried adjusting the formula to search all sheets, but haven't had good luck. Any thoughts?

#### Fluff

##### MrExcel MVP, Moderator
Any thoughts?
As this is the best part of 3 months old, not really. But it sounds as though this is a different question & therefore needs a new thread.

Replies
27
Views
459
Replies
7
Views
178
Replies
2
Views
135
Replies
2
Views
298
Replies
8
Views
162

1,191,700
Messages
5,988,173
Members
440,135
Latest member
DCDavid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back