Variable Table Reference Based on Cell Value

[wryan_garner4]

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.

 

[Toadstool]

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
	A	B	C	D
1	Location	Source	Expected Amount	Actual Amount
2	Canada	Wikipedia	500	450
3	Canada	Wikipedia	500	500
4	Egypt	Google	500	675
5	United States	Google	50	50
6	United States	Google	299	300
7
8
9
10
11
December

wryan_garner4.xlsx
	A	B	C
1	December
2
3	Source	Expected Amount	Actual Amount
4	Wikipedia	$ 1,000.00	$ 950.00
5	Google	$ 849.00	$ 1,025.00
6
7
Totals

Cell Formulas
Range	Formula
A4:A7	A4	=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:C7	B4	=IF($A4="","",SUMIF(INDIRECT($A$1&"["&$A$3&"]"),$A4,INDIRECT($A$1&"["&B$3&"]")))

 

[Fluff]

Another option for xl365

+Fluff 1.xlsm
	A	B	C
1	December
2
3	Source	Expected Amount	Actual Amount
4	Wikipedia	1000	950
5	Google	849	1025
Main

Cell Formulas
Range	Formula
A4:A5	A4	=LET(tbl,INDIRECT(A1&"["&A3&"]"),UNIQUE(FILTER(tbl,tbl<>"")))
B4:C5	B4	=SUMIFS(INDIRECT($A$1&"["&B$3&"]"),INDIRECT($A$1&"["&$A$3&"]"),$A4#)
Dynamic array formulas.

 

[wryan_garner4]

Another option for xl365

+Fluff 1.xlsm
	A	B	C
1	December
2
3	Source	Expected Amount	Actual Amount
4	Wikipedia	1000	950
5	Google	849	1025
Main

Cell Formulas
Range	Formula
A4:A5	A4	=LET(tbl,INDIRECT(A1&"["&A3&"]"),UNIQUE(FILTER(tbl,tbl<>"")))
B4:C5	B4	=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]

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.