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?

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

"Atishoo" wrote in message
...
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?

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

"David Biddulph" wrote:

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

"Atishoo" wrote in message
...
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?

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

"Atishoo" wrote in message
...
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

"David Biddulph" wrote:

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

"Atishoo" wrote in message
...
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?

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

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
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?

Fantastic!!
thanks very much

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
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?

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

On Feb 5, 12:44*pm, Atishoo wrote:
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?