My Vlookup solution is too clumsy (longish)

[Guest]

I currently have a workbook (sheet X) with 20 or so sheets, each sheet has
the same titles (Dates along the top, jobs down the left), one of these is a
"Consolidation" sheet, which adds the data of all the other sheets, the other
19 sheets are 1 for each person.

On a different excel document (sheet Y ) I plan on having a date on the top,
and doing a Hlookup of this date on the original workbook on the consolidated
sheet.

Example.
Tom 01/03/06 02/03/06
Job A 15 10
Job B 17 0

Jim 01/03/06 02/03/06
Job A 0 15
Job B 9 6

So on the consolidated sheet it would show the numbers 15, 25, 26, 6
respectively.
On the sheet Y, I could enter the date 01/03/06 and the Hlookups, would tell
me that 15 of Job A was done, and 26 of Job B.

But I also want it to tell me who has done the job. So

Date: 01/03/06
Job A 15 - Tom
Job B 26 - Tom & Jim

And I could change the date

Date: 02/03/06
Job A 25 - Tom & Jim
Job B 26 - Jim

For this, I initially thought of the formula:
=if(Tom!B2>0,1,0)+if(Jim!B2>0,2,0)
Then a Vlookup of this result against the table
0 Unallocated
1 Tom
2 Jim
3 Tom + Jim
For each job each day. This works fine but on the 16th person this table
needs to be 65535 rows deep, and requires a lot of inputting of all the names.

Can anyone help me with this?

 

[Guest]

My approach would be to use a single sheet as a master database, with columns
of NAME...DATE...JOB...QUANTITY
and do the "report genreation" by using the AutoFilter..........I hate those
"one sheet for each whatever, and a consolidation sheet" layouts.

hth
Vaya con Dios,
Chuck, CABGx3