Always show data from "Yesterdays" date from another worksheet

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)?
 
J

Joel

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
 
B

bran100

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top