Hello everyone,
This is my first post; please be gentle (if possible
).
I am trying to come up with a way, using an existing worksheet, to total the # of installations passed and failed, per 'Current Installer', per date. The date columns will continue to extend outwards (until they run out of space in 200 days...)
As it stands, there are Array Formulas (like the one in cell I15, for instance) that are 'interpreting' the entered text in Column 'I' and displaying a numeric value equal to the # of installations, etc. per date.
Incidentally, cell 'I10' should read "S & F", 'I11' should read "S & P" etc. - not sure why the nifty Excel HTLM Add-in did that.
I'd need to keep it broken down by date - I just need to further break down that value based on the Installer, if possible.
I tried to run a Pivot Table report, but it was ugly and unwieldy.
Also, since the report still needs the Date to be the main metric (a field wil be added daily), it was/would be a major pain to add so many different date fields to the Pivot Table layout (and there would be another to add, every day).
Any help would be greatly appreciated. If it can't be done (or if a solution would be much too involved), that is an answer I can take back to the bossman. I've been banging my head against a wall for about 5 hours now, and decided to ask for help. This is the place to go for Excel questions!
P.S. I didn't design or name the worksheet....
This is my first post; please be gentle (if possible
).I am trying to come up with a way, using an existing worksheet, to total the # of installations passed and failed, per 'Current Installer', per date. The date columns will continue to extend outwards (until they run out of space in 200 days...)
As it stands, there are Array Formulas (like the one in cell I15, for instance) that are 'interpreting' the entered text in Column 'I' and displaying a numeric value equal to the # of installations, etc. per date.
Incidentally, cell 'I10' should read "S & F", 'I11' should read "S & P" etc. - not sure why the nifty Excel HTLM Add-in did that.
I'd need to keep it broken down by date - I just need to further break down that value based on the Installer, if possible.
I tried to run a Pivot Table report, but it was ugly and unwieldy.
Also, since the report still needs the Date to be the main metric (a field wil be added daily), it was/would be a major pain to add so many different date fields to the Pivot Table layout (and there would be another to add, every day).
Any help would be greatly appreciated. If it can't be done (or if a solution would be much too involved), that is an answer I can take back to the bossman. I've been banging my head against a wall for about 5 hours now, and decided to ask for help. This is the place to go for Excel questions!
P.S. I didn't design or name the worksheet....
| Inspection_Log___Daily_Master__HENRRY.xls | ||||||||
|---|---|---|---|---|---|---|---|---|
| E | F | G | H | I | J | |||
| 6 | M | Tu | ||||||
| 7 | CurrentInstaller | DateInInspections | ReasonforFailedInspection | Previousto7/3/06 | 7/3 | 7/4 | ||
| 8 | ||||||||
| 9 | Andy/Tommy | 06/27/06 | Changeofplansrequiered | S-PU | ||||
| 10 | Jack/Mike | 05/04/06 | S&F | |||||
| 11 | Jaime | 06/28/06 | S&P | |||||
| 12 | MikeT. | 05/09/06 | S&F | |||||
| 13 | MikeT. | 04/07/06 | S&F | |||||
| 14 | ||||||||
| 15 | InspectionsPassed | 1 | 0 | |||||
| 16 | ||||||||
| 17 | InspectionsFailed-AllexceptNoAccess | 3 | 0 | |||||
| 18 | InspectionsFailed-NoAccess | 0 | 0 | |||||
| 19 | TotalFailedInspections | 3 | 0 | |||||
| 20 | ||||||||
| 21 | TotalPassed&Failed | 4 | 0 | |||||
Daily | ||||||||
