referencing to a sheet that it`s name is stored on a cell
 

Is there a way to change the sheet name in a formula with a name that
is stored in a cell?
I have a vlookup that looks in sheet1 range. I have in the Cell a10 the
name of the sheet I want to lookup the value.

Thanks
Gastón

referencing to a sheet that it`s name is stored on a cell
 

I'm not exactly sure of what you are trying to do. What is the purpose of
the worksheets.? Why did you put the name of the worksheet inside the cell?


You can give the cell a Name and have the name of the cell inside the formula.


Please provide more information about what you are trying to do.
------------------------------------------------------------------------------
----------------------------------------------------------------------
wrote:
Is there a way to change the sheet name in a formula with a name that
is stored in a cell?
I have a vlookup that looks in sheet1 range. I have in the Cell a10 the
name of the sheet I want to lookup the value.

Thanks
Gastón

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200611/1

referencing to a sheet that it`s name is stored on a cell
 

You should be able to use Indirect for that.

=VLOOKUP(A1,INDIRECT(A10 & "!A1:B2"),2)

"A1:B2" represents your lookup table.

HTH,
Paul

wrote in message
ups.com...
Is there a way to change the sheet name in a formula with a name that
is stored in a cell?
I have a vlookup that looks in sheet1 range. I have in the Cell a10 the
name of the sheet I want to lookup the value.

Thanks
Gastón

referencing to a sheet that it`s name is stored on a cell
 

Thanks for your help!
I use indirect but I can't work it out.
The problem is that I have the shhets in a different worksheet

When I use
=vlookup(A2,INDIRECT("[Libro2.xls]"& B2 &"!$A$1:$B$9"),2,0) It works
Fine

But if the worksheet have spaces in its name I get #!Ref
=Vlookup(A2,INDIRECT("[Libro 2 asd ewq.xls]"& B2 &"!$A$1:$B$9"),2,0)

Thanks




PCLIVE (RemoveThis) ha escrito:

You should be able to use Indirect for that.

=VLOOKUP(A1,INDIRECT(A10 & "!A1:B2"),2)

"A1:B2" represents your lookup table.

HTH,
Paul

wrote in message
ups.com...
Is there a way to change the sheet name in a formula with a name that
is stored in a cell?
I have a vlookup that looks in sheet1 range. I have in the Cell a10 the
name of the sheet I want to lookup the value.

Thanks
Gastón

referencing to a sheet that it`s name is stored on a cell
 

Thanks for your help!
I use indirect but I can't work it out.
The problem is that I have the shhets in a different worksheet

When I use
=vlookup(A2,INDIRECT("[Libro2.xls]"& B2 &"!$A$1:$B$9"),2,0) It works
Fine

But if the worksheet have spaces in its name I get #!Ref
=Vlookup(A2,INDIRECT("[Libro 2 asd ewq.xls]"& B2 &"!$A$1:$B$9"),2,0)

Thanks




PCLIVE (RemoveThis) ha escrito:

You should be able to use Indirect for that.

=VLOOKUP(A1,INDIRECT(A10 & "!A1:B2"),2)

"A1:B2" represents your lookup table.

HTH,
Paul

wrote in message
ups.com...
Is there a way to change the sheet name in a formula with a name that
is stored in a cell?
I have a vlookup that looks in sheet1 range. I have in the Cell a10 the
name of the sheet I want to lookup the value.

Thanks
Gastón

referencing to a sheet that it`s name is stored on a cell
 

twynsys

I have a workbook where I have sheets with information, one sheet per
month.
In another sheet I have names and months, I want to lookup for the
information in those months.

E.g.
Sheetname= april
name sales
jonh 3
charles 25
peter 1

Sheetname= March
name sales
jonh 33
charles 3
peter 8

in another workbook,

name month sales
john March 33
Peter April 1
Charles March 3

I want the vlookup formula to change the sheet in the formula according
to the column month.

Thanks twynsys.

referencing to a sheet that it`s name is stored on a cell
 

Try this:

=Vlookup(A2,INDIRECT("'[Libro 2 asd ewq.xls]"& B2 &"'!$A$1:$B$9"),2,0)

I think your workbook (Libro 2 asd ewq.xls) may need to be open in order to
calculate properly.

Regards,
Paul

wrote in message
ups.com...
Thanks for your help!
I use indirect but I can't work it out.
The problem is that I have the shhets in a different worksheet

When I use
=vlookup(A2,INDIRECT("[Libro2.xls]"& B2 &"!$A$1:$B$9"),2,0) It works
Fine

But if the worksheet have spaces in its name I get #!Ref
=Vlookup(A2,INDIRECT("[Libro 2 asd ewq.xls]"& B2 &"!$A$1:$B$9"),2,0)

Thanks




PCLIVE (RemoveThis) ha escrito:

You should be able to use Indirect for that.

=VLOOKUP(A1,INDIRECT(A10 & "!A1:B2"),2)

"A1:B2" represents your lookup table.

HTH,
Paul

wrote in message
ups.com...
Is there a way to change the sheet name in a formula with a name that
is stored in a cell?
I have a vlookup that looks in sheet1 range. I have in the Cell a10 the
name of the sheet I want to lookup the value.

Thanks
Gastón

referencing to a sheet that it`s name is stored on a cell
 

I have finally worked it out!
I was missing a ' .
Thanks all for your help!


ha escrito:

Thanks for your help!
I use indirect but I can't work it out.
The problem is that I have the shhets in a different worksheet

When I use
=vlookup(A2,INDIRECT("[Libro2.xls]"& B2 &"!$A$1:$B$9"),2,0) It works
Fine

But if the worksheet have spaces in its name I get #!Ref
=Vlookup(A2,INDIRECT("[Libro 2 asd ewq.xls]"& B2 &"!$A$1:$B$9"),2,0)

Thanks




PCLIVE (RemoveThis) ha escrito:

You should be able to use Indirect for that.

=VLOOKUP(A1,INDIRECT(A10 & "!A1:B2"),2)

"A1:B2" represents your lookup table.

HTH,
Paul

wrote in message
ups.com...
Is there a way to change the sheet name in a formula with a name that
is stored in a cell?
I have a vlookup that looks in sheet1 range. I have in the Cell a10 the
name of the sheet I want to lookup the value.

Thanks
Gastón