A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Pull Current Month's Data Out of List - Repost



 
 
Thread Tools Display Modes
  #1  
Old May 2nd 05, 05:08 AM
Karl Burrows
external usenet poster
 
Posts: n/a
Default Pull Current Month's Data Out of List - Repost

Hi! My original post just kind of slipped through the cracks.

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? I would prefer not to use autofilter.

Thanks!


Ads
  #2  
Old May 2nd 05, 07:37 AM
Toppers
external usenet poster
 
Posts: n/a
Default

Hi,
Is there any reason you aren't using VBA to to do the
extraction/summarising rather than use formulae - to avoid showing blank
rows?

Can you give a brief examples of both the raw data and the summary sheets
(I sssume there are these two sheets).

You could possibly use SUM or SUMPRODUCT to summarise data by MONTH,
CATEGORY etc.


"Karl Burrows" wrote:

> Hi! My original post just kind of slipped through the cracks.
>
> 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? I would prefer not to use autofilter.
>
> Thanks!
>
>
>

  #3  
Old May 2nd 05, 12:56 PM
Debra Dalgleish
external usenet poster
 
Posts: n/a
Default

Can you change the query in Access, so it only returns data for the
current month? Then, you wouldn't have to do any filtering in Excel.

Karl Burrows wrote:
> Hi! My original post just kind of slipped through the cracks.
>
> 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? I would prefer not to use autofilter.
>
> Thanks!
>
>



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #4  
Old May 3rd 05, 12:56 AM
Karl Burrows
external usenet poster
 
Posts: n/a
Default

The worksheet provides monthly sales over several years and this worksheet
just pulls current months sales.

"Debra Dalgleish" > wrote in message
...
Can you change the query in Access, so it only returns data for the
current month? Then, you wouldn't have to do any filtering in Excel.

Karl Burrows wrote:
> Hi! My original post just kind of slipped through the cracks.
>
> 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? I would prefer not to use autofilter.
>
> Thanks!
>
>



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


  #5  
Old May 3rd 05, 01:06 AM
Karl Burrows
external usenet poster
 
Posts: n/a
Default

I am not opposed to using VBA. It was just created using formulas
originally.

The raw data from Access consists of columns of sales division, salesperson,
item number, sales date and price. I added a column to the right to include
a column for a count (Basically "1" if cells to left have data and blank if
empty) for subtototals on other worksheets.

The sales worksheet is the same data just showing the current months data
only arranged in a sort order of division, salesperson, sales date. What I
did here was create formula in each column to basically show the data if the
database worksheet had corresponding current month data and blank if it was
from another period and then created a simple Macro to sort the data as
stated. I basically ended up sorting descending so the blanks went to the
bottom, so my data is reversed, but it was much easier that way.

Thanks!

"Toppers" > wrote in message
...
Hi,
Is there any reason you aren't using VBA to to do the
extraction/summarising rather than use formulae - to avoid showing blank
rows?

Can you give a brief examples of both the raw data and the summary sheets
(I sssume there are these two sheets).

You could possibly use SUM or SUMPRODUCT to summarise data by MONTH,
CATEGORY etc.


"Karl Burrows" wrote:

> Hi! My original post just kind of slipped through the cracks.
>
> 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? I would prefer not to use autofilter.
>
> Thanks!
>
>
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation list boxes Watson Excel Discussion (Misc queries) 4 May 26th 05 05:12 PM
Extracting/look up data from a list and select multiple instances Candice H. Excel Worksheet Functions 4 April 29th 05 04:38 PM
Extracting data from the current date Cali00 Excel Worksheet Functions 2 April 14th 05 05:49 AM
How do I create a list in excel that contains external data? bill@bb Excel Discussion (Misc queries) 1 February 15th 05 02:45 AM
How do I find data from a list (or table) and insert it in a row? Bobgolfs56 Excel Discussion (Misc queries) 1 February 3rd 05 12:51 AM


All times are GMT +1. The time now is 07:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.