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.

--
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

On Jan 4, 5:03*pm, van0710 wrote:
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.

--
van0710
*to beautiful years in a *beautiful city*

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
*to beautiful years in a beautiful city*


"van0710" wrote:

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.

--
van0710
*to beautiful years in a beautiful city*

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


"Pete_UK" wrote:

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

On Jan 4, 5:03 pm, van0710 wrote:
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.

--
van0710
*to beautiful years in a beautiful city*

You're welcome - thanks for feeding back.

Pete

On Jan 4, 5:39*pm, van0710 wrote:
Pete,
thank you; your formula is cleaner!
--
van0710
*to beautiful years in a *beautiful city*

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")

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"van0710" wrote:

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
*to beautiful years in a beautiful city*


"van0710" wrote:

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.

--
van0710
*to beautiful years in a beautiful city*