Always show data from "Yesterdays" date from another worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bran100 - 14 Mar 2007 15:55 GMT

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

Reply to this Message

Joel - 14 Mar 2007 17:50 GMT

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

> I have two worksheets
>
[quoted text clipped - 20 lines]
>
> How about yesterdays value for (ColB-ColC) (from worksheet2)?

Reply to this Message

bran100 - 14 Mar 2007 20:45 GMT

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 to this Message