In the attached please note:
There were trailing spaces in the list of Technicians and leading spaces in the date column (" *TOTAL*"). I cleaned those up to simplify this. In the process the dates which were text "dates" were converted to actual numeric dates.
I created some Dynamic Named Ranges (DNRs) in Name Manager. If you are not familiar with DNRs they size themselves automatically to fit their data. There is a small table included that can be deleted which shows how they are defined. It looks like this:
DATE.... |
=Sheet1!$B$2:INDEX(Sheet1!$B:$B,ROWS(Technician)+1) |
SOLD |
=Sheet1!$D$2:INDEX(Sheet1!$D:$D,MATCH("zzzzzzz",Sheet1!$A:$A)-7) |
Technician |
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH("zzzzzzz",Sheet1!$A:$A)-7) |
Then in A1 and copied down this formula and in B1 and copied down this formula
Bookmarks