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.

I was able to get something like this up:
=VLOOKUP(A1,one!$1:$65536,2,0)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"seans" wrote:
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.

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

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.

--

Dave Peterson

Thanks Dave you saved me a crapload of cut and paste

"Dave Peterson" wrote:

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

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.

--

Dave Peterson

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

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

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.

--

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

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

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

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.

--

Dave Peterson


--

Dave Peterson

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?

"Dave Peterson" wrote:

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

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

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

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.

--

Dave Peterson


--

Dave Peterson

Try http://xcell05.free.fr/morefunc/english/
--
David Biddulph

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

"Dave Peterson" wrote:

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

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

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

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.

--

Dave Peterson


--

Dave Peterson