| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calendar - vlookup based on dates | Excel Worksheet Functions | |||
| Code to hide tabs based on input | Excel Discussion (Misc queries) | |||
| How can I name Worksheet Tabs based on some king of reference? | Excel Worksheet Functions | |||
| Worksheet Tabs Names as Dates | Excel Worksheet Functions | |||
Linear Mode
