References to worksheets in formulae

A

andyiain

Hi,

I've got a vlookup and the range I want looked at can be one of four
tables, each of these is on a different sheet. Currently I either use
an if:
if(V2=1, vlookup..., if(V2=2, vlookup... and so on
or
I filter the data on the value that affects the range I want looked at
and only type then copy the correct formula in each case.

I was wondering, however, if there is any way to include the value in
the lookup table array. The reference to different worksheets is
"'-Name of steet'!-" Could I replace this somehow with a cell reference
so that if I named the sheets the same as the values that affect the
result this calculates automatically.

Or am I being daft and should carry on as I am.

Any help/guidance would be appreciated
Andy
 
F

Franz Verga

andyiain said:
Hi,

I've got a vlookup and the range I want looked at can be one of four
tables, each of these is on a different sheet. Currently I either use
an if:
if(V2=1, vlookup..., if(V2=2, vlookup... and so on
or
I filter the data on the value that affects the range I want looked at
and only type then copy the correct formula in each case.

I was wondering, however, if there is any way to include the value in
the lookup table array. The reference to different worksheets is
"'-Name of steet'!-" Could I replace this somehow with a cell
reference so that if I named the sheets the same as the values that
affect the result this calculates automatically.

Or am I being daft and should carry on as I am.

Any help/guidance would be appreciated
Andy
Click to expand...


I think you can use the INDIRECT function: if the sheets are in the same
workbook there's no problem, but if they are in different workbooks, the
workbok with the sheets need to be opened because otherwise the formula
returns the REF! error.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
A

andyiain

Hi Franz, thanks for the reply.

I'm not sure how one would use indirect here though. I thought
indirect would go to a cell reference, I need a reference to a
worksheet as part of the array reference in a vlookup.

Regards,
Andy
 
F

Franz Verga

andyiain said:
Hi Franz, thanks for the reply.

I'm not sure how one would use indirect here though. I thought
indirect would go to a cell reference, I need a reference to a
worksheet as part of the array reference in a vlookup.

Regards,
Andy
Click to expand...

Hi Andy,

You can "build" the reference to a worksheet using the indirect function;
for example, the formula:

=INDIRECT("'"&B4&"'!B15")

is a reference to the cell B15 of the sheet which name is in cell B4 of the
sheet in which the formula is written.

If you need more help, maybe you could upload an example file to
www.savefile.com

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
Top