Linking Data from 1 worksheet to another by using dates?

[Peter Raulin]

I have a daily sales workbook that I need to extract the sales from
yesterday, and have it filter to another worksheet for the current month. I
know to use the "Today()-1, to get the day before today. I need to know how
to get the information to filter onto another worksheet that will autofill
from the daily worksheet. Example worksheets "Daily", "Jan", "Feb", "Mar",
"YTD". I want to be able to use "Today()-1 to get the day befores date. Then
fill in to horizontal cells with dollar amounts, I have where they'll add
together on the "Daily", but how do I get the date and dollar amounts to fill
the respective month worksheets in succesion. Please help I'm pulling my hair
out!!!!!!!!!LOL

 

[joel]

Autofill won't work. A day is a number in excel.

Jan 1, 2009 = 39814
if you add 31 days you get
Feb 1, 2009 = 39845
if you add 28 days you get
Mar 1, 2009 = 39873
if you add 31 days you get
Apr 1, 2009 = 39904
if you add 30 days you get
May 1, 2009 = 39934

The point is you add 31, then 28, then 31, and then 30

Autofill expects the add number to be constant not changing in a random
fashion.



Now you can use a formula if you are smart

=Date(Colunm(),1,1) puts Jan in column A (1)

Then format the cell to show just the month

Now you can add an offset so that Jan appears in column C
=Date(Colunm()-2,1,1) column C is 3 so the month is 1