Not Sure What Formula to Use

[vikingreception]

Hi,

I have an Excel workbook with hundreds of sheets. Each sheet has a value in A4 that I want to populate in column 2 of an index sheet. The index sheet has all of the names of the sheets in the workbook in column 1 and I want the value from A4 on the matching sheet to populate in the cell next to the sheet name. How can I write a formula to do this? Does it have to be done with VBA?

Thanks,

Colleen

[#2 · Accepted solution]

@vikingreception 

Let's say the sheet names are in A2 and down on the index sheet.

Enter the following formula in B2:

=INDIRECT("'"&A2&"'!A4")

Fill down.

[#3]

Thank you so much!!

[#4]

@vikingreception  just for the fun of it.  here is an alternative if you don't have or want to make the list of all the sheets.  All you need is the names of the first and last sheet to make the 'range':

=LET(r,Sheet1:Sheet100!M8,
     str,TEXTJOIN(",",FALSE,r),
     L,LEN(str),
     n,L-LEN(SUBSTITUTE(str,",",""))+1,
     exploded,SUBSTITUTE(str,",",REPT(" ",L)),
     s,SEQUENCE(n,1,1,L),
     NUMBERVALUE(TRIM(MID(exploded,s,L))))

and soon we will be getting some new functions (SPLIT) that will make this even easier.