| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Always show data from "Yesterdays" date from another worksheet
I have two worksheets
Worksheet 1 has a summary of data that is gathered from worksheet 2 (detailed data) Worksheet 2 has the date in the A column and data for each day in subsequent columns... colA colB colC colD Jan1 1234 3214 5268 Jan2 1258 8387 6843 Jan3 9879 6188 1156 .... .... March14 6854 4654 6898 How do I get Worksheet 1 to always display "yesterdays" value for column B (from worksheet2)? ColA colB colC (yesterdays date) (value from ColB worksheet2) (Value of colB-colC from worksheet2) How about yesterdays value for (ColB-ColC) (from worksheet2)? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Always show data from "Yesterdays" date from another worksheet
The hard part is to get yesterdays' date. Can you make the date in Microsoft
time format rather than a text string? You can use format Mar-03 =VLOOKUP(TODAY()-1,A1:D10,2,FALSE) where today(1)-1 is yesterday "bran100" wrote: I have two worksheets Worksheet 1 has a summary of data that is gathered from worksheet 2 (detailed data) Worksheet 2 has the date in the A column and data for each day in subsequent columns... colA colB colC colD Jan1 1234 3214 5268 Jan2 1258 8387 6843 Jan3 9879 6188 1156 .... .... March14 6854 4654 6898 How do I get Worksheet 1 to always display "yesterdays" value for column B (from worksheet2)? ColA colB colC (yesterdays date) (value from ColB worksheet2) (Value of colB-colC from worksheet2) How about yesterdays value for (ColB-ColC) (from worksheet2)? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Always show data from "Yesterdays" date from another worksheet
Thanks for the direction. I had never used Vlookup before.
That actually worked perfect. =VLOOKUP((TODAY( ))-1,Sheet1!$A$1:$R$367,11,FALSE) Having fun with it now... Here's an average of the "last 3 days" =((VLOOKUP((TODAY( ))-1,Sheet1!$A$1:$R$367,11,FALSE))+ (VLOOKUP((TODAY( ))-2,Sheet1!$A$1:$R$367,11,FALSE))+ (VLOOKUP((TODAY( ))-3,Sheet1!$A$1:$R$367,11,FALSE)))/3 There may be an easier way to do that average, but it works fine for me. :) Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| EXCEL allow 2 options on status bar e.g. show "Count" + "Sum" | Excel Discussion (Misc queries) | |||
| "Show all" button not work in protected worksheet | Excel Worksheet Functions | |||
| if "a" selected from dropdown menu then show "K" in other cell | Excel Worksheet Functions | |||
| conditional formula to show "open" or "closed" | Excel Worksheet Functions | |||
| Show "create date" | Excel Discussion (Misc queries) | |||
Linear Mode
