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

G

GastonFranzini

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
 
T

twynsys via OfficeKB.com

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.
 
P

PCLIVE

You should be able to use Indirect for that.

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

"A1:B2" represents your lookup table.

HTH,
Paul

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
 
G

GastonFranzini

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

GastonFranzini

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

GastonFranzini

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.
 
P

PCLIVE

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

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

GastonFranzini

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


[email protected] 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

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
Click to expand...
Click to expand...
 
Top