Please see attached sample table if the below description isn't clear.
I have a list of records that are time block entries, (e.g. 8:00 a.m. to 9:30 a.m. = 1.5 hrs) assigned to a specific task number from 1 thru 7. So out of perhaps 50 entries I might have 7 task 1 entries, 18 task 2 entries, 4 task 3 entries, etc. The final column is the 4 digit invoice number that record is billed on. So the columns are:
col. B = time start (entry) (e.g. 8:00)
col. C = time end (entry) (e.g. 9:30)
col. D = hrs (calculated) (e.g. 1.5 hrs)
col. E = task # (entry, e.g. 7)
col. F = invoice # (entry, e.g. 4315)
My goal is to be able to do a sumif calculation for each task #1-10 totaling column 3 (total hrs) for each task. That's the easy part. But I also need to list for each task the individual invoice #s on which that task has been billed. End result being "16.5 hrs. billed for task 7 on invoices 4315, 4326 and 4410). All entries for a given task may have the same invoice #, or 4 records may have 4 different invoice # entries. So bottom line is that each task needs a collected list generated indicating the unique invoice entries (col. F) associated with any record for that task #(col. E).
Any ideas?
To make it even trickier, I would love for that list to appear in a single cell as a comma separated list (e.g. 4315,4326,4410) as opposed to a series of cells each containing one invoice #. This would be much easier to plan for and manage.
Thanks for any guidance.
Bookmarks