Software

Ask something
Discussion Locked

Excel summary sheet - possible?

0 helpful
3 replies

by blauber

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
We have an excel spreadsheet with many sheets, each corresponding to a different customer. CSRs fill in the fields on each page, including column that indicates if we "missed a promised date". It works well for them, but not for management, who wants a summary page of all the instances where we missed the mark. Is there a way to generate a summary page that looks at all sheets and whenever a cell is filled in (to indicate a missed deadline), it copies the entire line (or just certain cells fromit) over to the summary sheet?

3 replies

by khall3

Yes, there is but it is kind of hard how to tell you how to do it. What I would do is open all files up. Arrange them in a specific order using the Window, Arrange menu. Then in your summary sheet you are going to need an IF statement. This statement will reference the other cells in the other sheets. For example in a sheet called CSR you want to check a column named MISSED DEADLINE. Under that column header there are blank cells and in some there is the word MISSED for the deadlines thatwere missed. In order to calculate the instances were the word MISSED is shown you would use an IF statment in your summary sheet. In the first cell in your summary sheet you will use "=IF(" then you would click in the first cell is your CSR sheetwhere the first cell under your MISSED DEADLINE column header is. This insert that refernence into your IF STATEMENT. Then you would type "= "missed" after that reference. Then "," followed by what you want to do if the value is true then "," follwed by what you want to do if the value is false. Then fill the formula to the bottom using the fill handle in the bottom right of the cell.

by blauber

Okay, now I'm rolling! With your answer I can get the first part done. I inserted "=IF('#3864'!H5="missed",TRUE,FALSE)", and it works great.
The summary sheet now knows whether a line is true or false. (missed deadline or made it)

How do I get it to copy a "true" line over to the summary sheet? I want it to take that entire row and show it on the summary sheet, so that it displays a listing of all instances where "missed deadline" was "true".

This discussion is closed to new replies.