Vlookup , Referencing a cell to refence table array

[seans]

I ave multiple worksheet named one, two, three, four etc. In the worksheet
with the vlookup I need to be able to get the value in A1 say 'one' to
populate the table_array. It needs to say 'one'!$1:65536, All I can get it
to do is A1!$1:!65536.

 

[Max]

I was able to get something like this up:
=VLOOKUP(A1,one!$1:$65536,2,0)

 

[Dave Peterson]

Are you trying to vary the worksheet that's used in the =vlookup() by changing
the name of the worksheet in A1?

If yes, then maybe...

=vlookup(x99,indirect("'"&a1&"'!1:65536"),26,false)

if the value to look for is in x99 and you want to bring back the value in the
26th column (column Z).

 

[seans]

Thanks Dave you saved me a crapload of cut and paste

 

[James]

Hi Dave,
This partially fixes a problem I am having but I now have 2 other issues!
Can I specify a sheet ref. in the table_array (it will always be the same
ref. i.e sheet1)? Also it seems I need the workbooks open for it to work.
Any thoughts would be much appreciated.
Thanks,
James.

 

[Dave Peterson]

The value in A1 is the sheet name.

If you want to use a different workbook that's not open, then...

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

 

[James]

Thanks Dave, I have now mastered the INDIRECT function but the link is broken
to the addin! Do you have another link or is it possible for you to mail me
the addin directly?