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

bonconsul

New Member
Joined
Oct 11, 2019
Messages
14
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0
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)

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

#REF ! = no more sheets

Excel 2016 (Windows) 32 bit
A
B
C
D
E
1
SheetsName 2Name 3Name 4
#REF !​
2
=INDEX(ListSheets,COLUMN(B1))
3
B4
100​
200​
300​
4
=INDIRECT("'"& B1&"'!"&$A3)
Sheet: Name 1
 
Upvote 0
:oops: the formulas are in B1 and B3 copied across in above example
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top