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

#### Kilgore Trout

##### New Member
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.

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?

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

Replies
10
Views
150
Replies
1
Views
1K
Replies
1
Views
296
Replies
1
Views
94
Replies
0
Views
226

1,196,250
Messages
6,014,246
Members
441,808
Latest member
xplainer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back