How to insert a Sheet reference into a function

  • Thread starter Thread starter Chris Dunlap
  • Start date Start date
C

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
 
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
 
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 >
 
keepitcool said:
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))
Click to expand...
....

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.
 
keepitcool said:
I DONT THINK YOU NEED TO SHOUT WHEN YOU THINK YOU KNOW BETTER
Click to expand...

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.
Click to expand...

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.
 
Thanks guys, I was actually on that track but I had
omitted the "'".

Thx

-----Original Message-----
keepitcool said:
I DONT THINK YOU NEED TO SHOUT WHEN YOU THINK YOU KNOW
Click to expand...
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.
Click to expand...

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.


.
Click to expand...
 
Back
Top