Always show data from "Yesterdays" date from another worksheet

  • Thread starter Thread starter bran100
  • Start date Start date
B

bran100

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)?
 
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
 
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.
 
Back
Top