Summarize information on another sheet

[stocktsi]

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.

 

[Luke M]

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

 

[stocktsi]

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.

 

[Max]

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.

---

 

[stocktsi]

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.

 

[Gord Dibben]

I'm not Max but the link works for me.


Gord Dibben MS Excel MVP

 

[Max]

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=

 

[stocktsi]

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]

Welcome, good to hear that.