Formula help please...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have made a 33 page spreadsheet. One main for totals, 31 for daily data,
and a blank final page. I want to filter some data (first names, last,
customer account number from the daily pages to list on the final page only
if they made a purchase of a specific item. In other words, if all conditions
were met (all 4 columns were used) it would be sorted to the final page. Each
of the daily ones have 40 plus possible entries. I am looking specifically to
create an independant list on the last page if those particular 4 columns
have data. Anyone have a suggestion?
 
take a look at data>filters>adavanced filters.....it looksa little above my
head but it also looks very powerful
 
I tried that advance filter option. It said I had to name columns first. I
did that. But every time I try to highlite a range to be used it tells me I
have errored something. The help menu said I need 3 empty rows between the
column heading and the data. I am soooo confused!

It's sort of like (all the same on daily sheets 1-31)
column A is Customer number,
B is Last Name,
C is First Name.
Column G is an item whose value can only be 1.
The only thing I want to pull out is the A, B. C info if there is a 1
entered in column G. Duplicate entries don't need to be filtered out either.
I just want this data on a seperate page that shows me which customers
actually bought Item G

I have no idea what a pivot table is but I am going to read up on it.
 
It's a long time since I used 'advanced filter'. As far as I can remember
you require two columns. In one column you input the data fields (i.e.
columns) and in the adjacent column you an 'argument' which defines what to
include / exclude data in a particular field. It's very similar to
constructing a formula. You also have to specify a start cell for the
output.

Pivot tables essentially do the same thing. They are easier to construct
but lack the versatility - but that shouldn't be a problem. Get some data
and have a play with pivot tables and, hopefully, you will soon see how they
work.

Regards.

Bill Ridgeway
Computer Solutions
 
There shouldn't be any blank rows between the heading cells and the data.

Put your column headings in row 1, and start the data in row 2.
Copy the headings from column A, B and C
Go to the sheet where you want the filtered data to show
Select cell A1, and paste the copied headings
Go back to the data sheet, and copy the heading from column G

To set up the criteria area:
Go to the filter result sheet, select cell F1, and
paste the copied heading.
In cell F2, type a 1

On the filter result sheet, select a cell in the middle of the window,
away from the headings.
Choose Data>Filter>Advanced Filter
Choose Copy to Another location
For the list range, select the data on the source sheet
For the criteria range, select cells F1 and F2
For the copy to range, select cell A1:C1
Click OK

There are instructions and examples here:

http://www.contextures.com/xladvfilter01.html
 
You really are going to make things hard for yourself by spreading your
data across different sheets. You'd be far better off by having your data
on a single sheet, giving each days records a date field in another column
to identify them, and you could then easily analyse your data using filters
or Pivot tables. These things are sooooooo much easier with data in one
place.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
Ken wrote <<You'd be far better off by having your data on a single sheet
.... These things are sooooooo much easier with data in one place.>>

One problem with having data on several spreadsheets rather than on several
worksheets within a spreadsheet is that changes in structure (i.e. where
cells are located) are not changed (causing errors) whereas changes of
structure within worksheets changes are recognised by all the worksheets.

Regards.

Bill Ridgeway
Computer Solutions
 
Lots of wisdom offered to you by some top notch folks!

My 2 cents:

I hate pivot tables. Can a pivot table have 31 sheets as the source?

Filters are OK but are not dynamic. Using a filter would require you to
repeat the proccess 31 times. No joy!

If you are intent on the layout of the file structure then formulas could be
used for this but the output would not be exactly as you expect. You would
need to extract the desired data from each of the 31 sheets to it's own
location and because the amount of data extracted from each sheet will vary
there will be empty rows between data sets.

Biff
 
I understand the difficulty of so many sheets as the data sources but there
is no way around it. There is a lot of information entered on each daily page
(300 rows, column A-AV)and I need to be able to just pull those lists out. I
was originally leaning towards a sheet by sheet filter but it is not
practical for my needs. I'm going to try what Debra said and see it that
works. **keeping fingers crossed**
 
I thought that if each sheet had the same column headers on it, then the
newest version of Excel COULD generate pivot tables form multiple sheets. I
don't have an XP version of Excel, but perhaps someone else could
confirm...?
 
Excel said:
I have made a 33 page spreadsheet. One main for totals, 31 for daily data,
and a blank final page. I want to filter some data (first names, last,
customer account number from the daily pages to list on the final page only
if they made a purchase of a specific item. In other words, if all conditions
were met (all 4 columns were used) it would be sorted to the final page. Each
of the daily ones have 40 plus possible entries. I am looking specifically to
create an independant list on the last page if those particular 4 columns
have data. Anyone have a suggestion?
Click to expand...

Have you considered using Access? As I read this thread my first
thought was this is more suited for a database than a spreadsheet.

gls858
 
XP isn't the newest version, 2003 is. Either way, neither of them have a
friendly method of creating a Pivot table the way you would expect to be
able to if you use multiple sheets (called multiple consolidation ranges).
Even if all sheets are identical you will not see the columns you expect in
the field chooser.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
Back
Top