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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GastonFranzini@gmail.com - 27 Nov 2006 15:15 GMT

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

Reply to this Message

twynsys - 27 Nov 2006 15:49 GMT

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

>Is there a way to change the sheet name in a formula with a name that
>is stored in a cell?
[quoted text clipped - 3 lines]
>Thanks
>Gastón

Reply to this Message

GastonFranzini@gmail.com - 27 Nov 2006 17:12 GMT

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.

Reply to this Message

PCLIVE - 27 Nov 2006 16:11 GMT

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

Reply to this Message

GastonFranzini@gmail.com - 27 Nov 2006 17:04 GMT

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.
>
[quoted text clipped - 12 lines]
> Thanks
> Gastón

Reply to this Message

PCLIVE - 27 Nov 2006 17:55 GMT

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:

> You should be able to use Indirect for that.
>
[quoted text clipped - 14 lines]
> Thanks
> Gastón

Reply to this Message

GastonFranzini@gmail.com - 27 Nov 2006 17:07 GMT

> You should be able to use Indirect for that.
>
[quoted text clipped - 12 lines]
> Thanks
> Gastón

Reply to this Message

GastonFranzini@gmail.com - 27 Nov 2006 17:59 GMT

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

GastonFranzini@gmail.com ha escrito:

> Thanks for your help!
> I use indirect but I can't work it out.
[quoted text clipped - 27 lines]
> > Thanks
> > Gastón

Reply to this Message