Pull Only Current Month's Data From Another Worksheet

[Karl Burrows]

I have a workbook that contains a page that is linked to a database that
tracks all our product information and sales data. The import pulls in all
data for all periods where I have other worksheets that track the history by
product. I also have another worksheet that shows current month's sales.
Right now, I have to guess how many total products and sales we have had and
will have to create enough rows of formulas to look for the current month's
sales (the formulas look at each row and if the sales period is within the
current month, it shows the data, otherwise the row is left blank). Current
products and sales are about 2000, so I created 2500 formulas to make sure I
had enough.

It seems to me there is a way to look at the database tab and just list the
sales data on the other worksheet without having to create an excess of
formulas either through VBA or using some kind of range and offset formula,
but I just can't figure it out.

Any help would be greatly appreciated!!

 

[Frank Kabel]

Hi
some ideas:
- you may use 'Data - filter - advanced filter' to get only the
relevant information for a month
- maybe pivot tables are what you're looking for. See
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
to get some starting information

 

[Karl Burrows]

My group can't handle using Pivot Tables and I have the data formatted with
totals at the top and error checks to make sure the data was input
correctly, so not really an option I want to try.

Thanks for the thought!

 

[Ken Wright]

Just a thought and I haven't really tried it, but what about another column
(hidden if necessary) on your source data sheet that refers to the date of that
record and does something like:-

assuming your dates are in Col B, and your helper column is Col A in A1 put

=IF(DATE(YEAR(TODAY()),MONTH(TODAY()),1)=DATE(YEAR(A1),MONTH(A1),1),65537-((DATE
(YEAR(TODAY()),MONTH(TODAY()),1)=DATE(YEAR(B1),MONTH(B1),1))*ROW()),0)

and copy down

This will put a series of values in that column such as

65536
65535
0
0
65532
0 etc

Then you could just use the LARGE() function in combination with ROW() on your
destination sheet to bring across those flags for records that are not 0 (ie
meet your criteria), eg:-

On dest sheet in say A1,

=IF(LARGE(Sheet1!A:A,ROW())=0,"",LARGE(Sheet1!A:A,ROW()))

and copy down as far as necessary to cover max rows

Then use some combination of VLOOKUP or INDEX/MATCH etc or whatever, to bring in
the data you need based on those uniqe flag numbers. I don't see a way of
limiting the rows containing formulas though as you will need to cover the max
possible rows, but at least you won't have redundant rows aof blanks
interspersed with your valid data. You could also perhaps put some kind of flag
in there that will let you know if your formula doesn't cover enough rows to
show all the data.

 

[Karl Burrows]

That works, but I still have to 'anticipate' how many rows/records could
possibly populate the database tab, so the simpler formula I created works
as well:

IF(DATE=CurrentMonth, SalesData,"")

Then I created a simple Macro to sort descending so the blank rows drop to
the bottom. My data is reverse, but at least it's at the top and somewhat
sorted.

Then I fill the rows like you mentioned. This is a tough one. I should
probably create it in Access, but they want to pull it in Excel.

Also, shouldn't I put formulas to the right of the database import so the
formulas will fill down as records are added?

Thanks for the suggestion!!