My Vlookup solution is too clumsy (longish)

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?

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





"PaulW" wrote:

> 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?
>