|
Vlookup Different tabs based on Dates
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! |
Vlookup Different tabs based on Dates
Try it like this:
=IF(ISNUMBER(B7),"",VLOOKUP(B6&" Total", INDIRECT("'"&TEXT(A6,"mmm-dd")&"'!B:E"),4,0)) -- Biff Microsoft Excel MVP "andy" wrote in message ... 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! |
Vlookup Different tabs based on Dates
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" wrote: Try it like this: =IF(ISNUMBER(B7),"",VLOOKUP(B6&" Total", INDIRECT("'"&TEXT(A6,"mmm-dd")&"'!B:E"),4,0)) -- Biff Microsoft Excel MVP "andy" wrote in message ... 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! |
Vlookup Different tabs based on Dates
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. -- Biff Microsoft Excel MVP "andy" wrote in message ... 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" wrote: Try it like this: =IF(ISNUMBER(B7),"",VLOOKUP(B6&" Total", INDIRECT("'"&TEXT(A6,"mmm-dd")&"'!B:E"),4,0)) -- Biff Microsoft Excel MVP "andy" wrote in message ... 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! |
Vlookup Different tabs based on Dates
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" wrote: 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. -- Biff Microsoft Excel MVP "andy" wrote in message ... 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" wrote: Try it like this: =IF(ISNUMBER(B7),"",VLOOKUP(B6&" Total", INDIRECT("'"&TEXT(A6,"mmm-dd")&"'!B:E"),4,0)) -- Biff Microsoft Excel MVP "andy" wrote in message ... 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! |
Vlookup Different tabs based on Dates
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "andy" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP "andy" wrote in message ... 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" wrote: Try it like this: =IF(ISNUMBER(B7),"",VLOOKUP(B6&" Total", INDIRECT("'"&TEXT(A6,"mmm-dd")&"'!B:E"),4,0)) -- Biff Microsoft Excel MVP "andy" wrote in message ... 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! |
| All times are GMT +1. The time now is 11:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com