using strings in vlookup function calls

[AtTheEndofMyRope]

Hi.

I am having success using vlookup on a sheet (Week 1).

The vlookup formula is as: =VLOOKUP(B8,'Week 1'!A2:F17,4,FALSE)

It properly returns "Thu" from column 4 of the mentioned array in the
sheet named "Week 1".

B8 sequences up in the array as it is the lookup reference value.

My copied array of that formula also works.

I want to replace 'Week 1' sheet name declaration with a call to a
sheet whose name is stored in a cell like "B2". The cell contents matches
the sheet name exactly And is chosen from a drop down list validation
schema at present.

I have 17 sheets of data, and one sheet to examine them in, and the drop
down list allows choosing a sheet (week), I want that drop down cell
content to be the replacement text in the vlookup formula. That way the
sheet contents reflects the selected week's data.

Also, it would be nice to reference a named array in the "A2:F17" part
of the formula like "Week 1" through "Week 17" in the formula, also based
on the contents selected for B2.

I cannot remember how to declare the string so that excel looks at it
correctly.

 

[Pete_UK]

Try this:

=VLOOKUP(B8,INDIRECT("'"&B2&"'!A2:F17"),4,FALSE)

Note that if you use named ranges for the tables then spaces are not
allowed - Excel will substitute an underscore for the space. If your
ranges are named uniquely (i.e. you only have one Week_3 for example)
then you will not need to bother with the sheet name. So, if you have
the name Week_3 in B2, and that refers to $A$2:$F$17 on the sheet
named Week3, then you should be able to do it with:

=VLOOKUP(B8,INDIRECT(B2),4,FALSE)

Hope this helps.

Pete

 

[AtTheEndofMyRope]

Thank you. I will attempt each, but I will have to rename my sheets
first. Calling the named array directly does sound better though.

I'll just likely skip to method.

Thanks again.

 

[AtTheEndofMyRope]

Thank you, your solutions worked perfectly.

I now have a new problem.

Vlookup will not carry an image over from a cell contents.

Is there a way to transport the image in a cell using a vlookup to
handle the variable value of the referenced cell?

 

[Gord Dibben]

Images do not reside in cells.........they just lay on top.

To return an image using a VLOOKUP function see John McGimpsey's lookuppics
sample workbook.

http://www.mcgimpsey.com/excel/lookuppics.html


Gord Dibben MS Excel MVP

 

[Pete_UK]

Thanks for feeding back - glad the formulae worked for you.

Gord has given you the same link that I would have for your problem
with pictures.

Pete