Vlookup Different tabs based on Dates

[andy]

I am looking for a vlookup that will look up different tabs based on dates.
I have been looking through the INDIRECT questions but I cannot get my
formula to work.

I have a report that I run everyday and save this report as different tabs
with the appropriate date as the tab name. I need the net change from
day-to-day. I need to lookup the previous days data (saved as a tab with
that date) and then subtract it from the current days data.

Here are 2 different versions I am working on:
=IF(ISNUMBER(B7),"",VLOOKUP(B6&" Total",INDIRECT(""&A6&"'!A:B'"),4,FALSE))

=IF(ISNUMBER(B7),"",VLOOKUP(B6&"
Total","'"&INDIRECT(TEXT(A6,"m-dd"))&"'!B:E",4,FALSE))

These formuals are on my current days tab which is Apr-20. Cell A6 shows
the current date of Apr-20. I would like this formula to vlookup the
infromation from Apr-19.
Thanks!

 

[T. Valko]

Try it like this:

=IF(ISNUMBER(B7),"",VLOOKUP(B6&" Total",
INDIRECT("'"&TEXT(A6,"mmm-dd")&"'!B:E"),4,0))

 

[andy]

Cell A6 is my current days date of 4-20. I would like this formula to
vlookup the previous days date of 4-19 which is the label of a tab.

My current tab is 4-20 with all of 4-20's information, I would like to
vlookup today's date -1 which would be the 4-19 tab. The current formula I
am using shows a #REF error.

 

[T. Valko]

OK, just subtract 1 from A6:

If A6 contains the true Excel date 4/20/2009

=IF(ISNUMBER(B7),"",VLOOKUP(B6&" Total",
INDIRECT("'"&TEXT(A6-1,"mmm-dd")&"'!B:E"),4,0))

That refers to the sheet named Apr-19.

 

[andy]

Awesome! It works! I had 1 parenthesis in the wrong place. Here is the
final formula:

=IF(ISNUMBER(B7),"",VLOOKUP(B6&"
Total",INDIRECT("'"&TEXT(A6-1,"m-dd")&"'!B:E"),4,0))

Thanks!

 

[T. Valko]

You're welcome. Thanks for the feedback!