VLOOKUP and INDIRECT formula giving #REF error

[ChelseaRanee]

Hi,

Hoping that someone can help me out. I've been tearing my hair out over this formula and feel like the answer is staring me in the face but I'm too close to see it.

I am trying to have excel find the most recent time that a product arrived and enter that date into a cell. Each month of orders is listed on a separate worksheet. We will only store the last 3 months of orders and the sheets will be labelled 1month, 2months, and 3months (these will change as each month is added hence the indirect formula). I want the formula to check the lists in order of most recent then move on to the next month if no result is found. I'm getting a #REF error but can't see why. Can someone look over this and tell me what I'm missing, I'm sure that it will be super obvious. Failing that, is there another way to make the sheet search names static when I'll ne changing the names of the tabs?

Formula I'm using is =VLOOKUP(A2,INDIRECT("'"&$B$1&"'!"&"$A$1:$G$598"),7,FALSE)

I've attached the error evaluation breakdown too


-- removed inline image ---



-- removed inline image ---



-- removed inline image ---

 

[BarryL]

are there spaces in your sheet names? if not then you don't need the apostrophes

=VLOOKUP(A2,INDIRECT($B$1&"!$A$1:$G$598"),7,FALSE)

 

[ChelseaRanee]

are there spaces in your sheet names? if not then you don't need the apostrophes

=VLOOKUP(A2,INDIRECT($B$1&"!$A$1:$G$598"),7,FALSE)

There was but there isn't now. I got rid of the spaces to see if I'd mistyped the apostrophes. I've changed to your formatting now but still getting a #REF error. Thanks for your help!

 

[ChelseaRanee]

<a href="http://s1045.photobucket.com/user/ChelseaRanee/media/Excel%201.png.html" target="_blank"><img src="http://i1045.photobucket.com/albums/b455/ChelseaRanee/Excel%201.png" border="0" alt=" photo Excel 1.png"/></a>

<a href="http://s1045.photobucket.com/user/ChelseaRanee/media/Excel%202.png.html" target="_blank"><img src="http://i1045.photobucket.com/albums/b455/ChelseaRanee/Excel%202.png" border="0" alt=" photo Excel 2.png"/></a>

<a href="http://s1045.photobucket.com/user/ChelseaRanee/media/Evaluate%20Formula.png.html" target="_blank"><img src="http://i1045.photobucket.com/albums/b455/ChelseaRanee/Evaluate%20Formula.png" border="0" alt=" photo Evaluate Formula.png"/></a>

Pics that didn't work above, where can I find the edit button to clean up my original post?

 

[Aladin Akyurek]

It's probably due to the fact that the sheet the formula is trying to look at does not exist...

 

[ChelseaRanee]

I've got tabs with the same names as the headers in the sheet I'm working from(they should be visible in one of the pics). I even changed the name of both to "A" to see if I'd mistyped one but still getting an error. Thanks for your help!

 

[ChelseaRanee]

Ugh. I worked it out. The tab for 1month had a space at the end but the header didn't. I knew it was going to be something super dumb and obvious. Thanks for all the help