Populate sheet with data from other sheets - How? Pls Help

[Kilgore Trout]

Hi there, here is one thing I have been struggling with for some time:

I have Sheets One to Twenty, each dedicated to reporting sales activities with a different customer. Column 'I' in every sheet reports status of a different sales item per row, with either "Pending" or "Done".

What I would need is to have in a new sheet all items flagged as "Pending" sorted by customer. In that way I would have a snapshoot of all pending items, rather than having to go through all 20 sheets.

Thanks. KT

 

[Mooresmi]

This can be done. You need to give each "pending" job a number, say 1, though an if statement running down the whole page for each of you twenty pages.

This then will allow for a ranking formula to be created on your "summary page". You would have forty columns on this page (two for each customer). The only jobs appearing would be the "pending" jobs.

Sorry I can't upload spreadsheet as network prevents me from downloading HtmlMaker.

But here is what you need to do.

Customer 1

Customer page
Customer Status Score Rank
ABC Pending 3 6
DEF Done 0 9
GHI Pending 5 5
JKL Pending 6 4
MNO Pending 7 3
PQR Pending 8 2
STU Done 0 9
VWX Done 0 9
YZ Pending 11 1
Hide score & rank columns


Score =IF(B3="Pending",ROW(),ROW()/1000000)
Rank =RANK(C3,C$3:C$11)+COUNTIF(C$3:C$11,C3)-1

Summary Page

ID
Pending
Customer Status
YZ Pending
PQR Pending
MNO Pending
JKL Pending
GHI Pending
ABC Pending
VWX Done
STU Done
DEF Done
ID=OFFSET('Customer 1'!$A$3,MATCH(SMALL('Customer 1'!$D$3:$D$11,ROW()-ROW($B$3)+1),'Customer 1'!$D$3:$D$11,0)-1,0)

Status =VLOOKUP(A3,'Customer 1'!$A$3:$B$11,2,0)

Then Conditional format these two columns so that Pending only shows!

You will have to work hard with this, but if you send me your email address, I will send you the spreadsheet which will make it easier. Could you then load it up here so that all can see?

 

[Kilgore Trout]

Thanks for the response and sheet, which is very comprehensive, however it doesn't solve my problem as sheets are updated almost daily with new rows. I cannot get to have the summary sheet updated with new information as soon as this is inputed in the source sheets. Would be interesting if you had a solution for that.

What I did in the meanwhile was to link rows of the source sheets to the summary sheet and extract the "pending" information through a pivot table.

Best Regards. KT