References to worksheets in formulae

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

andyiain wrote:
> 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


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

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

andyiain wrote:
> 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

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