Vlookup and sheet name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BC - 15 Jan 2008 01:42 GMT

Looking for some help,
I have Multiple Sheets all named with people names
I have a master sheet with this formula
=IF($A2="","",VLOOKUP($C$1,Bozo!$D$14:$K$236,8,FALSE))
works great except I need to change the sheet name all the time "Bozo" to
the different sheet names.
The sheet name is already on this same sheet in Cell A2 But
=IF($A2="","",VLOOKUP($C$1,A2!$D$14:$K$236,8,FALSE))
Does not work.
Sorry if this is a silly question, im new to excel and Ive been searching
for days triyng to find the answer, Thanks for the help BC

Reply to this Message

carlo - 15 Jan 2008 03:19 GMT

What you need is the indirect formula:

=IF($A2="","",VLOOKUP($C$1,indirect(A2 & "!$D$14:$K$236",8,FALSE))

hth

Carlo

> Looking for some help,
> I have Multiple Sheets all named with people names
[quoted text clipped - 7 lines]
> Sorry if this is a silly question, im new to excel and Ive been searching
> for days triyng to find the answer, Thanks for the help BC

Reply to this Message

BC - 15 Jan 2008 12:46 GMT

Thank you Carlo for the reply, I tryed your formula and I get a you entered
too many arguments for this fuction error.

> What you need is the indirect formula:
>
[quoted text clipped - 15 lines]
> > Sorry if this is a silly question, im new to excel and Ive been searching
> > for days triyng to find the answer, Thanks for the help BC

Reply to this Message

carlo - 16 Jan 2008 00:26 GMT

Hey BC

sorry, my mistake, forgot to close the brackets:

=IF($A2="","",VLOOKUP($C$1,indirect(A2 & "!$D$14:$K$236"),8,FALSE))

> Thank you Carlo for the reply, I tryed your formula and I get a you entered
> too many arguments for this fuction error.
[quoted text clipped - 20 lines]
>
> - Show quoted text -

Reply to this Message

Dave Peterson - 16 Jan 2008 00:32 GMT

Sometimes, worksheet names have to be surrounded by apostrophes (embedded
spaces, numbers, names that look like addresses):

=IF($A2="","",VLOOKUP($C$1,indirect("'" & A2 & "'!$D$14:$K$236"),8,FALSE))

> Hey BC
>
[quoted text clipped - 26 lines]
> >
> > - Show quoted text -

Signature

Dave Peterson

Reply to this Message

carlo - 16 Jan 2008 01:27 GMT

Thanks for pointing that out Dave, totally forgot to include that!!!

Carlo

> Sometimes, worksheet names have to be surrounded by apostrophes (embedded
> spaces, numbers, names that look like addresses):
[quoted text clipped - 37 lines]
>
> - Show quoted text -

Reply to this Message

BC - 16 Jan 2008 23:46 GMT

Thank you for the help! Works great! BC

> Thanks for pointing that out Dave, totally forgot to include that!!!
>
[quoted text clipped - 41 lines]
> >
> > - Show quoted text -

Reply to this Message