Reading sheet name from specific cell

[Bartonsen]

I have a workbook with multiple sheets where I would like to read specific (same) cell values from every sheet, and add them to a Summary sheet.

I know this can be done by typing 'sheet name!cell', but I have 100+ sheets, and I read 50-100 cells from each.
The way its done for "Martin" and "John" in the picture is the way I know about.
But can it be done in a way like for "Debbie" and "Leah" ?

Ie., I would like to have the sheet names in column A, and then use the same code for all columns B..Z, ie CELL C5 should show the value from sheet=Leah, CELL=B207.

New sheets are added on a regular basis, and adding sheet name for every column B..Z each time is very time consuming, when you can't just copy the previous line....

Any suggestions?

2021-06-02_170108.png

[etaf]

a spreadsheet attached would be better, see yellow banner. then i could have added to the spreadsheet this example
you can use indirect() to add the sheet name

=Indirect($A2&"!"&B$1)

[Bartonsen]

Thanks Wayne, exactly what I needed!

[etaf]

you are welcome

[Bartonsen]

May I ask one more question with reference to the attached Indirect-ETAF.xlsx?

What if Sheet2 contain an "x" in C1, and Sheet3 contains an "x" in E1, is it then possible on Sheet1, to show the cellposition of the x?
Something like this:
Sheet A1 A2 C1:E1
Sheet2 Sheet2 A1 Sheet2 A2 C1
Sheet3 Sheet3 A1 Sheet3 A2 E1

The x needs to be in position C1:E1

Or even better:
If the "x" is in C1, it should return the character a
If the "x" is in D1, it should return the character b
If the "x" is in E1, it should return the character c

So that sheet1 would look like this:
Sheet A1 A2 C1:E1
Sheet2 Sheet2 A1 Sheet2 A2 a
Sheet3 Sheet3 A1 Sheet3 A2 c

[etaf]

not sure i'm fully following, it may also be worth starting a new thread, as more chance of other members seeing , and in addition also adding spreadsheet , and include manually adding the expected results.