Function: indirect to call other Worksheet names ad a date in mmmm-yyyy format

[Miki2]

Hello,
I've looked for other reply regarding the use of this function. I tried many possible solutions but they don't work.

I have a dynamic Sheet (named as previous month, i.e. March-2015) and I need to do a vlookup with the previous sheet (e.i. February-2015).

I tried this function:
=VLOOKUP(B2;INDIRECT("$O$4"&"!B:B");1;0) - where O4 report the name February-2015. It doesn't work with Date (if I put Sheet2 instead, it works).

I also tried this function:
=VLOOKUP(B2;INDIRECT("'"&$O$4&"'!B:B");1;0) but it doesn't work either.

I'm looking for any possible way to make a vlookup with previous month (always in position 3 on the file).

Thank you!

 

[Andrew Poulsom]

Welcome to MrExcel.

What does:

=ISNUMBER(O4)

return?

 

[Miki2]

welcome to mrexcel.

What does:

=isnumber(o4)



return?

it returns = true

 

[Jonmo1]

I think Andrew is onto the actual problem..

But It's worth nothing that in this formula

=VLOOKUP(B2;INDIRECT("$O$4"&"!B:B");1;0)

If your intention is to use the contents of the cell O4 as the name of the sheet, then O4 should NOT be in quote marks.
With $O$4 in quote marks, it will be looking for a sheet literally named "$O$4"

That should be
=VLOOKUP(B2;INDIRECT($O$4&"!B:B");1;0)

 

[Miki2]

I think Andrew is onto the actual problem..

But It's worth nothing that in this formula

If your intention is to use the contents of the cell O4 as the name of the sheet, then O4 should NOT be in quote marks.
With $O$4 in quote marks, it will be looking for a sheet literally named "$O$4"

That should be
=VLOOKUP(B2;INDIRECT($O$4&"!B:B");1;0)

Hello and thank you for your answer.
I've just put the formula in excel and returns #REF!

 

[Jonmo1]

Since ISNUMBER(O4) returned TRUE
That means it actually contains a DATE, but custom cell formatting made it show as FEBRUARY-2015

Try
=VLOOKUP(B2;INDIRECT(TEXT($O$4,"mmmm-yyyy")&"!B:B");1;0)

 

[Miki2]

Since ISNUMBER(O4) returned TRUE
That means it actually contains a DATE, but custom cell formatting made it show as FEBRUARY-2015

Try
=VLOOKUP(B2;INDIRECT(TEXT($O$4,"mmmm-yyyy")&"!B:B");1;0)

I also tried this earlier... because text returns me the right name... but if I use indirect, it doesn't work anymore and give me back #REF! too.

Are there any other possible way to do it?

 

[Andrew Poulsom]

It need some single quotes:

=VLOOKUP(B2,INDIRECT("'"&TEXT($O$4,"mmmm-yyyy")&"'!B:B"),1,0)

 

[Miki2]

It need some single quotes:

=VLOOKUP(B2,INDIRECT("'"&TEXT($O$4,"mmmm-yyyy")&"'!B:B"),1,0)

It works perfectly!!!
Thank you so much