How to insert a Sheet reference into a function

[Chris Dunlap]

Is there a way in insert a sheet reference into a function
via a data validation list?

A simple example would be a series of sheets (Sheet2-
Sheet25) with data in cell B5

I need a function on Sheet1 that will call B5 from the
desired sheet, say Sheet20 via a selection from a dropdown
list on Sheet1. Is this clear?

Thx

 

[Rick C]

Chris,
After setting up the List Box, you would want a formula something like this:
=INDIRECT(INDEX($A$1:$A$24)&"!$B$5")

where A1:A24 contains the names of the sheets (this is the input range for
your list box).

hth,
Rick

 

[keepitcool]

Chris,

see help for ADDRESS and INDIRECT worksheet functions.
ADDRESS will accept sheetname as a variable.

final function will look like
=indirect(address(row,col,sheet))


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Harlan Grove]

see help for ADDRESS and INDIRECT worksheet functions.
ADDRESS will accept sheetname as a variable.

final function will look like
=indirect(address(row,col,sheet))

....

WAY TOO VAGUE! Also inefficient.

=INDIRECT("'"&WorksheetName&"'!B5")

would work with only one function call. INDIRECT(ADDRESS(..)) is almost
always a mistake in the sense that it could be functionally replaced with
fewer function calls without loss of clarity.

 

[Harlan Grove]

I DONT THINK YOU NEED TO SHOUT WHEN YOU THINK YOU KNOW BETTER

I SHOUT WHEN I WANT TO!

indirect can be very usefull, as you
dont need to hardcode the Cell address
thus you can easily copy the code.

I didn't question INDIRECT - it's the only way to convert text to range
references. I question the use of ADDRESS. It's almost always unnecessary.
R1C1 text addresses can always be used instead.
INDIRECT(ADDRESS(ROW(),COLUMN()-X)) is equivalent to
INDIRECT("RC["&-X&"]",0). ADDRESS is only useful when you want the *final*
result to be text.

 

[Chris]

Thanks guys, I was actually on that track but I had
omitted the "'".

Thx

-----Original Message-----

I DONT THINK YOU NEED TO SHOUT WHEN YOU THINK YOU KNOW

BETTER

I SHOUT WHEN I WANT TO!

indirect can be very usefull, as you
dont need to hardcode the Cell address
thus you can easily copy the code.

I didn't question INDIRECT - it's the only way to convert text to range
references. I question the use of ADDRESS. It's almost always unnecessary.
R1C1 text addresses can always be used instead.
INDIRECT(ADDRESS(ROW(),COLUMN()-X)) is equivalent to
INDIRECT("RC["&-X&"]",0). ADDRESS is only useful when you want the *final*
result to be text.


.