Vlookup problem

[Peter]

Hi Guys, I am trying to get this formula to work, without much joy. I have
several pages of data, each with a month as their sheet name, this is
collected from the "month" sheet from a drop down menu in cell B2. The
summary sheet where this formula is used will look for the category in G4
then go to the month sheet described by MONTH!$B$2 and search for the match
for G4 then return the data from the 6th column in the data area described
by B4:M500. All I get is #VALUE. I have spent hours playing with this and
although I hate to give in to this problem I have just run out of energy.
Any help would be gratefully received

VLOOKUP(G4,(MONTH!$B$2&"B4:M500"),6,FALSE)

Regards
Peter

 

[Dave Peterson]

So Month!B2 holds a plain old worksheet name...

If yes, then maybe...

=VLOOKUP(G4,indirect("'" & MONTH!$B$2 & "'!B4:M500"),6,FALSE)

 

[Peter]

Hi Dave, many thanks, saved my sanity, but I still do not understand the
("'" & bit of the solution....
Many thanks for your solution
Peter

 

[Dave Peterson]

Start a new workbook with two sheets
Rename sheet1 to "sheet 1" (with the space, without the "'s)

Select A1 on that sheet
edit|copy

Select A1 on Sheet2
edit|paste special|click paste link

Look at the formula in the formula bar.

You'll see that some worksheet names have to be surrounded by apostrophes in
formulas--if they contain formulas, if they look like numbers, if they look like
addresses....

And if your worksheet needs them, but you don't supply them, then the formula
breaks.

If your worksheet doesn't need them and you supply them, no harm done.

 

[Dave Peterson]

You'll see that some worksheet names have to be surrounded by apostrophes in
formulas--if they contain formulas, if they look like numbers, if they look like
addresses....

should have been:

You'll see that some worksheet names have to be surrounded by apostrophes in
formulas--if they contain SPACES, if they look like numbers, if they look like
addresses....