formula to get the value of the same cell but in different sheets

[bonconsul]

Hello,

I have a workbook with plenty of sheets named 'name 1', name 2', name 3,...
One sheet is supposed to be an overview of the inputs from all those different sheets.
So for example I want the value of cell B2 of all the sheets 'name....) in the overview sheet.

Is this possible without a macro?

Wouter

 

[steve the fish]

You will need to put the sheet name in a cell then do something like:

=INDIRECT("'"&A1&"'!B2")

where A1 holds the name of the sheet you are interested in.

 

[bonconsul]

Awesome! Thanks @steve the fish!!

 

[Yongle]

and developing on previous post ...
the names of the sheets can be autogenerated with this trick

Create a named Range with
Name: ListSheets
RefersTo: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Formula in cell B2 copied across
=INDEX(ListSheets,COLUMN(B1))

Formula in cell B4 copied across
=INDIRECT("'"& B1&"'!"&$A3)

UNFORTUNATELY the formulas in row 1 do not auto-refresh - so will need refreshing if a sheet is inserted or deleted later

#REF ! = no more sheets

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Sheets[/td][td]Name 2[/td][td]Name 3[/td][td]Name 4[/td][td]

#REF !​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td][/td][td]=INDEX(ListSheets,COLUMN(B1))[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]B4[/td][td]

100​

[/td][td]

200​

[/td][td]

300​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td]=INDIRECT("'"& B1&"'!"&$A3)[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Name 1[/td][/tr][/table]

 

[Yongle]

the formulas are in B1 and B3 copied across in above example