create overview page; lookup results from multiples worksheet

[van0710]

I have a workbook with many sheets and for every sheet I want to lookup the
values of multiple cells. With the address function I can create which cells
I want to lookup; but how can I get the value in those cells returned?

short descriptive:
worksheet names are 3039, 3058 (and more)
cell B2 of each sheet contains a name (say name is EXAMPLE on sheet 3039 and
EXHIBIT on sheet 3058)
cell B4 of each sheet contains a number (say number is 250 on sheet 3039 and
430 on sheet 3058)
I want to create an overview on a new sheet that shows me the values of
those cells.

 

[Pete_UK]

In column A of your overview sheet you can list the sheet names that
you want to get data from - assume this starts in A2. Then in B2 you
can have this formula:

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

and this one in C2:

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

(Note the apostrophes). Then just copy these two formulae down for as
many sheets as you have listed in column A. If you have blanks in any
of those cells, the formula will return zero - you can avoid this by:

B2: =IF(INDIRECT("'"&A2&"'!B2")="","",INDIRECT("'"&A2&"'!B2"))
and:
C2: =IF(INDIRECT("'"&A2&"'!B4")="","",INDIRECT("'"&A2&"'!B4"))

and then copy down.

Hope this helps.

Pete

Hope this helps.

Pete

 

[van0710]

IT FINALLY STRUCK ME; SORRY FOR ANSWERING MY OWN QUESTION; HOPEFULLY IT IS
HELPFULL FOR OTHERS IN THE FUTURE.

=INDIRECT(ADDRESS(2,2,1,1,$A2),TRUE)
WHERE A2 IS WORKSHEET NAME (E.G. EXHIBIT)
RESULTS VALUE IN CELL B2 OF SHEET EXHIBIT

** NOTE THAT $A2 CAN ALSO BE LOOKED UP **

 

[van0710]

Pete,
thank you; your formula is cleaner!
--
van0710
*to beautiful years in a beautiful city*

In column A of your overview sheet you can list the sheet names that
you want to get data from - assume this starts in A2. Then in B2 you
can have this formula:

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

and this one in C2:

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

(Note the apostrophes). Then just copy these two formulae down for as
many sheets as you have listed in column A. If you have blanks in any
of those cells, the formula will return zero - you can avoid this by:

B2: =IF(INDIRECT("'"&A2&"'!B2")="","",INDIRECT("'"&A2&"'!B2"))
and:
C2: =IF(INDIRECT("'"&A2&"'!B4")="","",INDIRECT("'"&A2&"'!B4"))

and then copy down.

Hope this helps.

Pete

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

 

[Shane Devenshire]

Hi,

You can shorten your formula to read

=INDIRECT(ADDRESS(2,2,,,A2))

if your sheet names do not contain spaces you can use this shortened version
of an earlier suggestion

=INDIRECT(A2&"!B2")