Pull Current Month's Data Out of List

Hi!

I have a workbook that pulls sales from an Access database. The data is set
to update each time the workbook is opened. I created a summary worksheet
to look at the data tab and just show current month's sales by category
which is basically a formula for each row to make current sales visible and
then I have a simple Macro to sort the data. There are a few issues I have
here. First, I had to create formulas for thousands of rows to make sure I
have enough rows to cover data in the data tab, so if the sales totals
exceed the rows I have for the sales summary, then I miss sales. Second,
for some reason now, when the Access data updates, it messes up the formulas
in the summary tab and I get #ERR in the formulas. Third, my Macro just
sorts the data bring it to the top so the blank rows go to the bottom. I
have to sort in inverse order, so the summary is backwards.

A little background on formulas:
* I have named ranges for the Access data tab that are using OFFSET so all
data is included in the named range regardless of how many rows it fills.
* The formula in the summary is just a formula that looks at each row and
checks to see if it is within the current month and then shows the
description, date and sales price. If it is not in the current month, it is
just blank.
* The sort Macro just resorts the data range (about 2500 rows) descending so
the blanks go to the bottom rows.

What I would like to do is figure out a way to have the summary page just
find the monthly totals for that month in ascending order. Is there a way
to do this so I can avoid creating thousands of rows of formulas and just
have it fill in the data?

Thanks!

Hi

Why don't you filter on the month Karl and copy to a new sheet ?
See this Add-in
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Karl Burrows" <[email protected]> wrote in message news:ue%[email protected]...
> Hi!
>
> I have a workbook that pulls sales from an Access database. The data is set
> to update each time the workbook is opened. I created a summary worksheet
> to look at the data tab and just show current month's sales by category
> which is basically a formula for each row to make current sales visible and
> then I have a simple Macro to sort the data. There are a few issues I have
> here. First, I had to create formulas for thousands of rows to make sure I
> have enough rows to cover data in the data tab, so if the sales totals
> exceed the rows I have for the sales summary, then I miss sales. Second,
> for some reason now, when the Access data updates, it messes up the formulas
> in the summary tab and I get #ERR in the formulas. Third, my Macro just
> sorts the data bring it to the top so the blank rows go to the bottom. I
> have to sort in inverse order, so the summary is backwards.
>
> A little background on formulas:
> * I have named ranges for the Access data tab that are using OFFSET so all
> data is included in the named range regardless of how many rows it fills.
> * The formula in the summary is just a formula that looks at each row and
> checks to see if it is within the current month and then shows the
> description, date and sales price. If it is not in the current month, it is
> just blank.
> * The sort Macro just resorts the data range (about 2500 rows) descending so
> the blanks go to the bottom rows.
>
> What I would like to do is figure out a way to have the summary page just
> find the monthly totals for that month in ascending order. Is there a way
> to do this so I can avoid creating thousands of rows of formulas and just
> have it fill in the data?
>
> Thanks!
>
>

I am trying to automate it as much as possible as the people that are
pulling these reports just want to pull it up automatically and are not very
proficient at Excel. Plus, I have a check and balance with another
worksheet so I can check for database errors to make sure the totals are
correct. I would prefer to do it with a formula or Macro if possible. Any
suggestions? Thanks!

"Ron de Bruin" <[email protected]> wrote in message
news:[email protected]...
Hi

Why don't you filter on the month Karl and copy to a new sheet ?
See this Add-in
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Karl Burrows" <[email protected]> wrote in message
news:ue%[email protected]...
> Hi!
>
> I have a workbook that pulls sales from an Access database. The data is
> set
> to update each time the workbook is opened. I created a summary worksheet
> to look at the data tab and just show current month's sales by category
> which is basically a formula for each row to make current sales visible
> and
> then I have a simple Macro to sort the data. There are a few issues I
> have
> here. First, I had to create formulas for thousands of rows to make sure
> I
> have enough rows to cover data in the data tab, so if the sales totals
> exceed the rows I have for the sales summary, then I miss sales. Second,
> for some reason now, when the Access data updates, it messes up the
> formulas
> in the summary tab and I get #ERR in the formulas. Third, my Macro just
> sorts the data bring it to the top so the blank rows go to the bottom. I
> have to sort in inverse order, so the summary is backwards.
>
> A little background on formulas:
> * I have named ranges for the Access data tab that are using OFFSET so all
> data is included in the named range regardless of how many rows it fills.
> * The formula in the summary is just a formula that looks at each row and
> checks to see if it is within the current month and then shows the
> description, date and sales price. If it is not in the current month, it
> is
> just blank.
> * The sort Macro just resorts the data range (about 2500 rows) descending
> so
> the blanks go to the bottom rows.
>
> What I would like to do is figure out a way to have the summary page just
> find the monthly totals for that month in ascending order. Is there a way
> to do this so I can avoid creating thousands of rows of formulas and just
> have it fill in the data?
>
> Thanks!
>
>