Excel: Indirect() - Populate summary sheet from other named sheets in spreadsheet

Question

How do I use INDIRECT() [or another function] in excel to combine a name sheet reference and cell reference?

Problem Context:

I have an excel spreadsheet which contains a set of named sheets:

 [1] "S10000_R3.3.2_201703301839"  "S10000_T4.3.0_201703301843" 
 [3] "S20000_R3.3.2_201703301826"  "S20000_T4.3.0_201703301832" 
 [5] "S30000_R3.3.2_201703301803"  "S30000_T4.3.0_201703301817" 
 [7] "S40000_R3.3.2_201703301738"  "S40000_T4.3.0_201703301752" 
 [9] "S50000_R3.3.2_201703301707"  "S50000_T4.3.0_201703301724" 
[11] "S60000_R3.3.2_201703301624"  "S60000_T4.3.0_201703301647" 
[13] "S70000_R3.3.2_201703301535"  "S70000_T4.3.0_201703301602" 
[15] "S80000_R3.3.2_201703301430"  "S80000_T4.3.0_201703301508" 
[17] "S90000_R3.3.2_201703301324"  "S90000_T4.3.0_201703301400" 
[19] "S260000_R3.3.2_201704021725" "S260000_T4.3.0_201704021929"
[21] "S270000_R3.3.2_201704021241" "S270000_T4.3.0_201704021529"
[23] "S280000_R3.3.2_201704020847" "S280000_T4.3.0_201704021100"
[25] "S290000_R3.3.2_201704020447" "S290000_T4.3.0_201704020702"
[27] "S310000_R3.3.2_201704012331" "S310000_T4.3.0_201704020242"
[29] "S320000_R3.3.2_201704011827" "S320000_T4.3.0_201704012128"
[31] "S330000_R3.3.2_201704011304" "S330000_T4.3.0_201704011546"
[33] "S340000_R3.3.2_201704010652" "S340000_T4.3.0_201704011010"
[35] "S350000_R3.3.2_201704010020" "S350000_T4.3.0_201704010404"
[37] "S360000_R3.3.2_201703311819" "S360000_T4.3.0_201703312134"
[39] "S370000_R3.3.2_201703310914" "S370000_T4.3.0_201703311301"
[41] "S380000_R3.3.2_201703310134" "S380000_T4.3.0_201703310509"
[43] "S390000_R3.3.2_201703301846" "S390000_T4.3.0_201703302252"

Each sheet contains a set of timing data...

function.   user    system  elapsed
DT read input   2.144   0.63    12.274
DT Convert Date to Date Format  0.263   0.065   1.104
DT select via scan  0.024   0.001   0.047

I also have a sheet named hidden that has a list of the sheet names.

Objective

I want to be able to populate a summary sheet from each of the sheets using cell references. I would love to be able to enter one formula on the summary spreadsheet and drag right so that the sheet references increment...

Example:

I want to be able to combine the sheet name with a cell reference that returns the value on the referenced sheet.

For example:

=S10000_R3.3.2_201703301839!A1 will return 'function' based on the above example data. i.e. Return value referenced at cell A1 on sheet S10000_R3.3.2_201703301839

now I could use the hidden sheet to return the sheet names...

=Hidden!A1 will return S10000_R3.3.2_201703301839

Problem:

I cannot seem to combine the sheet reference with a cell reference where the cell reference will increment when I pull down or right. I appear to be creating a single string not a combination of sheet name and cell reference.

=INDIRECT("'[Analysis.xlsx]hidden'!A1") will return S10000_R3.3.2_201703301839

I want to now combine that value return with a cell reference...

S10000_R3.3.2_201703301839!A1

Question:

How do I formulate the formula?

Should I use nested INDIRECT(hidden'!A1INDIRECT or something else?

the hard part appears to be how to allow the cell reference to increment as it is a combination of string concatenation and a cell reference.

Answer 1

Your INDIRECT gets the sheet name but not also the cell on that sheet, so you need to add that bit on the end:

=INDIRECT('[Analysis.xlsx]hidden'!A1 & "!A1")

If you want to be able to drag down and/or accross, experiment with the ADDRESS function. Something like this:

=INDIRECT('[Analysis.xlsx]hidden'!A1 & "!" & ADDRESS(ROW(A1),COLUMN(A1)))