worksheet reference

[Atishoo]

I want to return the contents of a cell on a seperate worksheet refered to by
name in another cell:

so if cell c7 ="policy"

and I want cell mainpage!D10 to return the value in Policy!H11

or if cell c7 ="calls"

then I want cell mainpage!D10 to return the value in calls!H11

any ideas?

 

[David Biddulph]

=IF(C7="policy",Policy!H11,IF(C7="calls",calls!H11,"undefined output"))

 

[Bob Phillips]

=INDIRECT("'"&C7&"'!H11")

 

[Pete_UK]

You can do it like this in D10 on mainpage sheet:

=INDIRECT("'"&C7&"'!H11")

Note the apostrophe between the double quotes and before the ! - these
cater for your sheet names having spaces.

Hope this helps.

Pete

 

[Atishoo]

Unfortunately i dont know what the worksheet names are necessarily going to
be as new worksheets are generated by a sub I am using, The worsheet name
will depend on a vlue added by the end user.
Policy and calls were just examples.
So I need a way of referencing a worksheet by the value (which may be
unknown and variable) in a cell

 

[Atishoo]

Fantastic!!
thanks very much

=INDIRECT("'"&C7&"'!H11")

--
__________________________________
HTH

Bob

 

[David Biddulph]

In which case the INDIRECT function, as suggested by others, is certainly
your best bet.