Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarize information on another sheet
I've got a workbook set up with multiple tabs. Each tab represents a
different slice of detailed information that's auto-filtered based on a status in a master tab (ie - all info in the master tab, different filters in the other tabs.) I'd like to create a summary tab that shows a subset of the data on the individual tabs. I've been able to display counts of the information (countif...), but don't know how to display a couple of columns of the information. For example: master tab "1" tab "2" tab ------------ -------- -------- joe 1 2 3 joe 1 2 3 ron 2 6 7 sam 1 4 5 sam 1 4 5 kim 2 7 8 ron 2 6 7 abe 2 9 9 kim 2 7 8 abe 2 9 9 I'd like the summary tab to look like: task 1 task 2 ----------- -------- 2 people 3 people joe ron sam kim abe If I use an If statement to screen rows out, I get blank rows. If I use filters, the rows don't match so data isn't shown. Is there a way to do this without creating a macro to screen things out and condense them? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarize information on another sheet
I think a pivot table will be able to do something similar to what you are
looking for. You would need to set it up to run off of the master sheet. The one down side, it would set things up transposed from your example, task 1 joe sam task 2 rob kim abe -- Best Regards, Luke M "stocktsi" wrote: I've got a workbook set up with multiple tabs. Each tab represents a different slice of detailed information that's auto-filtered based on a status in a master tab (ie - all info in the master tab, different filters in the other tabs.) I'd like to create a summary tab that shows a subset of the data on the individual tabs. I've been able to display counts of the information (countif...), but don't know how to display a couple of columns of the information. For example: master tab "1" tab "2" tab ------------ -------- -------- joe 1 2 3 joe 1 2 3 ron 2 6 7 sam 1 4 5 sam 1 4 5 kim 2 7 8 ron 2 6 7 abe 2 9 9 kim 2 7 8 abe 2 9 9 I'd like the summary tab to look like: task 1 task 2 ----------- -------- 2 people 3 people joe ron sam kim abe If I use an If statement to screen rows out, I get blank rows. If I use filters, the rows don't match so data isn't shown. Is there a way to do this without creating a macro to screen things out and condense them? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarize information on another sheet
Thanks for the advice. I was hoping to go "vertical" because there are going
to be lots of items under each task (potentially 20 - 30). If it's set up using a pivot table as you have below, I think it will scroll off the page. Any other suggestions? Thanks. "Luke M" wrote: I think a pivot table will be able to do something similar to what you are looking for. You would need to set it up to run off of the master sheet. The one down side, it would set things up transposed from your example, task 1 joe sam task 2 rob kim abe -- Best Regards, Luke M "stocktsi" wrote: I've got a workbook set up with multiple tabs. Each tab represents a different slice of detailed information that's auto-filtered based on a status in a master tab (ie - all info in the master tab, different filters in the other tabs.) I'd like to create a summary tab that shows a subset of the data on the individual tabs. I've been able to display counts of the information (countif...), but don't know how to display a couple of columns of the information. For example: master tab "1" tab "2" tab ------------ -------- -------- joe 1 2 3 joe 1 2 3 ron 2 6 7 sam 1 4 5 sam 1 4 5 kim 2 7 8 ron 2 6 7 abe 2 9 9 kim 2 7 8 abe 2 9 9 I'd like the summary tab to look like: task 1 task 2 ----------- -------- 2 people 3 people joe ron sam kim abe If I use an If statement to screen rows out, I get blank rows. If I use filters, the rows don't match so data isn't shown. Is there a way to do this without creating a macro to screen things out and condense them? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarize information on another sheet
Here's a thought, illustrated in this sample:
http://www.freefilehosting.net/download/NDYwNTE= Summarize col A info from multiple shts.xls Assume 2 source tabs named simply as numbers: 1, 2 where col A contains the key data to be extracted into the summary sheet (data in col A may be interspersed with blank cells) In a helper sheet: R, Put in A1: =IF(INDEX(INDIRECT(COLUMNS($A:A)&"!A:A"),ROWS($1:1 ))="","",ROW()) Copy A1 to B1, fill down to cover the max expected extent of data in any source tab's col A, eg down to B100 Then in your summary sheet, List the 2 source tab names in A2:B2, viz: 1, 2 Put in A3: =IF(ROWS($1:1)COUNT('R'!A:A),"",INDEX(INDIRECT("' "&A$2&"'!A:A"),SMALL('R'!A:A,ROWS($1:1)))) Copy A3 across to B3, fill down to the same extent as done in R. This will return the desired results from each of the source tabs' col A, with all results neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "stocktsi" wrote: Thanks for the advice. I was hoping to go "vertical" because there are going to be lots of items under each task (potentially 20 - 30). If it's set up using a pivot table as you have below, I think it will scroll off the page. Any other suggestions? Thanks. "Luke M" wrote: I think a pivot table will be able to do something similar to what you are looking for. You would need to set it up to run off of the master sheet. The one down side, it would set things up transposed from your example, task 1 joe sam task 2 rob kim abe -- Best Regards, Luke M "stocktsi" wrote: I've got a workbook set up with multiple tabs. Each tab represents a different slice of detailed information that's auto-filtered based on a status in a master tab (ie - all info in the master tab, different filters in the other tabs.) I'd like to create a summary tab that shows a subset of the data on the individual tabs. I've been able to display counts of the information (countif...), but don't know how to display a couple of columns of the information. For example: master tab "1" tab "2" tab ------------ -------- -------- joe 1 2 3 joe 1 2 3 ron 2 6 7 sam 1 4 5 sam 1 4 5 kim 2 7 8 ron 2 6 7 abe 2 9 9 kim 2 7 8 abe 2 9 9 I'd like the summary tab to look like: task 1 task 2 ----------- -------- 2 people 3 people joe ron sam kim abe If I use an If statement to screen rows out, I get blank rows. If I use filters, the rows don't match so data isn't shown. Is there a way to do this without creating a macro to screen things out and condense them? Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarize information on another sheet
Max,
That sounds like what I'm trying to do. However, the link to the sample xls doesn't work so I couldn't look at what you did. I tried to implement based on what you had below, but wasn't able to get it to work. Could you check the link so I can use your spreadsheet as a reference? Thanks. "Max" wrote: Here's a thought, illustrated in this sample: http://www.freefilehosting.net/download/NDYwNTE= Summarize col A info from multiple shts.xls Assume 2 source tabs named simply as numbers: 1, 2 where col A contains the key data to be extracted into the summary sheet (data in col A may be interspersed with blank cells) In a helper sheet: R, Put in A1: =IF(INDEX(INDIRECT(COLUMNS($A:A)&"!A:A"),ROWS($1:1 ))="","",ROW()) Copy A1 to B1, fill down to cover the max expected extent of data in any source tab's col A, eg down to B100 Then in your summary sheet, List the 2 source tab names in A2:B2, viz: 1, 2 Put in A3: =IF(ROWS($1:1)COUNT('R'!A:A),"",INDEX(INDIRECT("' "&A$2&"'!A:A"),SMALL('R'!A:A,ROWS($1:1)))) Copy A3 across to B3, fill down to the same extent as done in R. This will return the desired results from each of the source tabs' col A, with all results neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "stocktsi" wrote: Thanks for the advice. I was hoping to go "vertical" because there are going to be lots of items under each task (potentially 20 - 30). If it's set up using a pivot table as you have below, I think it will scroll off the page. Any other suggestions? Thanks. "Luke M" wrote: I think a pivot table will be able to do something similar to what you are looking for. You would need to set it up to run off of the master sheet. The one down side, it would set things up transposed from your example, task 1 joe sam task 2 rob kim abe -- Best Regards, Luke M "stocktsi" wrote: I've got a workbook set up with multiple tabs. Each tab represents a different slice of detailed information that's auto-filtered based on a status in a master tab (ie - all info in the master tab, different filters in the other tabs.) I'd like to create a summary tab that shows a subset of the data on the individual tabs. I've been able to display counts of the information (countif...), but don't know how to display a couple of columns of the information. For example: master tab "1" tab "2" tab ------------ -------- -------- joe 1 2 3 joe 1 2 3 ron 2 6 7 sam 1 4 5 sam 1 4 5 kim 2 7 8 ron 2 6 7 abe 2 9 9 kim 2 7 8 abe 2 9 9 I'd like the summary tab to look like: task 1 task 2 ----------- -------- 2 people 3 people joe ron sam kim abe If I use an If statement to screen rows out, I get blank rows. If I use filters, the rows don't match so data isn't shown. Is there a way to do this without creating a macro to screen things out and condense them? Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarize information on another sheet
I'm not Max but the link works for me.
Gord Dibben MS Excel MVP On Wed, 5 Dec 2007 17:10:03 -0800, stocktsi wrote: Max, That sounds like what I'm trying to do. However, the link to the sample xls doesn't work so I couldn't look at what you did. I tried to implement based on what you had below, but wasn't able to get it to work. Could you check the link so I can use your spreadsheet as a reference? Thanks. "Max" wrote: Here's a thought, illustrated in this sample: http://www.freefilehosting.net/download/NDYwNTE= Summarize col A info from multiple shts.xls Assume 2 source tabs named simply as numbers: 1, 2 where col A contains the key data to be extracted into the summary sheet (data in col A may be interspersed with blank cells) In a helper sheet: R, Put in A1: =IF(INDEX(INDIRECT(COLUMNS($A:A)&"!A:A"),ROWS($1:1 ))="","",ROW()) Copy A1 to B1, fill down to cover the max expected extent of data in any source tab's col A, eg down to B100 Then in your summary sheet, List the 2 source tab names in A2:B2, viz: 1, 2 Put in A3: =IF(ROWS($1:1)COUNT('R'!A:A),"",INDEX(INDIRECT("' "&A$2&"'!A:A"),SMALL('R'!A:A,ROWS($1:1)))) Copy A3 across to B3, fill down to the same extent as done in R. This will return the desired results from each of the source tabs' col A, with all results neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "stocktsi" wrote: Thanks for the advice. I was hoping to go "vertical" because there are going to be lots of items under each task (potentially 20 - 30). If it's set up using a pivot table as you have below, I think it will scroll off the page. Any other suggestions? Thanks. "Luke M" wrote: I think a pivot table will be able to do something similar to what you are looking for. You would need to set it up to run off of the master sheet. The one down side, it would set things up transposed from your example, task 1 joe sam task 2 rob kim abe -- Best Regards, Luke M "stocktsi" wrote: I've got a workbook set up with multiple tabs. Each tab represents a different slice of detailed information that's auto-filtered based on a status in a master tab (ie - all info in the master tab, different filters in the other tabs.) I'd like to create a summary tab that shows a subset of the data on the individual tabs. I've been able to display counts of the information (countif...), but don't know how to display a couple of columns of the information. For example: master tab "1" tab "2" tab ------------ -------- -------- joe 1 2 3 joe 1 2 3 ron 2 6 7 sam 1 4 5 sam 1 4 5 kim 2 7 8 ron 2 6 7 abe 2 9 9 kim 2 7 8 abe 2 9 9 I'd like the summary tab to look like: task 1 task 2 ----------- -------- 2 people 3 people joe ron sam kim abe If I use an If statement to screen rows out, I get blank rows. If I use filters, the rows don't match so data isn't shown. Is there a way to do this without creating a macro to screen things out and condense them? Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarize information on another sheet
Ah, the link is good but for some reason
you can't click direct on it from MS's webpage Just do a direct copy n paste of the link inclusive of the trailing "=", into your browser address bar: http://www.freefilehosting.net/download/NDYwNTE= -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "stocktsi" wrote: Max, That sounds like what I'm trying to do. However, the link to the sample xls doesn't work so I couldn't look at what you did. I tried to implement based on what you had below, but wasn't able to get it to work. Could you check the link so I can use your spreadsheet as a reference? Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarize information on another sheet
Thanks - the link works, and I was able to look at the spredsheet. I've been
able to modify what you have to work on my spreadsheet. Works great. Thanks for your help! "Max" wrote: Ah, the link is good but for some reason you can't click direct on it from MS's webpage Just do a direct copy n paste of the link inclusive of the trailing "=", into your browser address bar: http://www.freefilehosting.net/download/NDYwNTE= -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "stocktsi" wrote: Max, That sounds like what I'm trying to do. However, the link to the sample xls doesn't work so I couldn't look at what you did. I tried to implement based on what you had below, but wasn't able to get it to work. Could you check the link so I can use your spreadsheet as a reference? Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarize information on another sheet
Welcome, good to hear that.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Dec 7, 3:38 am, stocktsi wrote: Thanks - the link works, and I was able to look at the spredsheet. I've been able to modify what you have to work on my spreadsheet. Works great. Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet Information - Is sheet protected? | Excel Discussion (Misc queries) | |||
How do I automatically get information from sheet 1 into sheet 2 | Excel Discussion (Misc queries) | |||
Display rows meeting criteria on another sheet & summarize them | Excel Discussion (Misc queries) | |||
summarize data sheet | Excel Worksheet Functions | |||
Summarize multiple worksheet detail on summary sheet | Excel Discussion (Misc queries) |