I'm using Excel 2007. On the attached spreadsheet, I'm really only concerned with the formulas that I have in the grey shaded area.
The formula is correctly displaying the 1st, 2nd, 3rd & 4th helper (person) for each PO# (Job) listed vertically in the columns to the left. I need the helpers for each PO# repeated for each occurrence (day) of that same PO# so that I can properly summarize this data in the pivot table I've included on the attached example.
As you can see, since not each occurrence of the same PO# has a helper, the the formula is displaying a zero in the grey shaded area for that blank cell. I wish for the formula to ignore the blank helper cell instead of counting that as the Nth occurrence. In the attached example, I want helper John Doe (cell H2) and helper Mary Jane (cell I2) to be shifted to the left under the Helper 1 and Helper 2 columns since the are really the 1st and 2nd occurrence of a helper for PO# H00843 (there was no helper on the first day of the job). This is a little hard to explain in words but I think the attached spreadsheet will make it much clearer.
I appreciate any help that you can provide.
Bookmarks