Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula help please...
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? |
#2
|
|||
|
|||
take a look at datafiltersadavanced filters.....it looksa little above my
head but it also looks very powerful -- paul remove nospam for email addy! "Excel Dummy" wrote: 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? |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 DataFilterAdvanced 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 he http://www.contextures.com/xladvfilter01.html Excel Dummy wrote: 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. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
|
|||
|
|||
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 :-) ------------------------------*------------------------------*---------------- "Excel Dummy" <Excel wrote in message ... 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? |
#8
|
|||
|
|||
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 "Ken Wright" wrote in message ... 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 :-) ------------------------------*------------------------------*---------------- "Excel Dummy" <Excel wrote in message ... 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? |
#9
|
|||
|
|||
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 "Excel Dummy" <Excel wrote in message ... 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? |
#10
|
|||
|
|||
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** "Biff" wrote: 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 "Excel Dummy" <Excel wrote in message ... 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? |
#11
|
|||
|
|||
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 Dummy" wrote in message ... 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** "Biff" wrote: 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 "Excel Dummy" <Excel wrote in message ... 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? |
#12
|
|||
|
|||
Excel Dummy wrote:
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? 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 |
#13
|
|||
|
|||
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 :-) ------------------------------*------------------------------*---------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |