VLookup Multiple Sheets - along single row across 52 sheets

Afternoon all....

I'm attempting to create a management tool in the style of a 'database' and I'm using excel - I should probably have learnt access but I am where I am ~ and I love Excel.

Basically from two input sheets (where staff and job details are entered) I've managed to create a series of 52 weekly sheets to display the hours that each staff memebr will spend on each job over the next 52 weeks - when it's live etc...

I now want to creata a summary sheet that will look to the relevant sheet i.e. sheets W(1) to W(52) and return the number of hours by week of each staff member (regardless of which scheme they are working on) i.e. a staff usability by week calculator.

I've tried using a couple of different UDFs from forums, that scan each sheet looking for the date in question within a defined cell range across multiple sheets, but they are failing... Possibly because the dates are not unique to just the sheets I wish to search, although they are unique to the cell range I'm specifying?!?
For info they are:
VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num as Integer, Optional Range_look as Boolean)
MultVlookup(FindThis As Variant, LookIn As Range, SheetRange As String,OffsetColumn As Integer)

I've also attempted an INDIRECT command which I extracted from elsewhere, which refers to the sheet and cell range I wish to perform my VLOOKUP on i.e.:
=VLOOKUP(A2,INDIRECT(A1),3,FALSE)
Where A2 is the date I'm looking at, A1 is the "sheet:cell" reference in text format: i.e. W(12)!$C$6:$Z$6, and I'd like the 3rd column of this 1 row vlookup range returning... but still no joy.

Can anyone out there give me a hand with this one please?
I'm begging before something goes pop in my head!!
Cheers
Poolio

Dom, thanks for the tip, normally I would try to keep everything together, but my lists will increase (unprecitably in length over time), so I wouldn't know where to stop one and start the next ~ although thinking abuot it with 65,000 lines to play with I think I'd probably have enough room!
JP

Dom,
Thanks for your super-prompt help thus far... but I just can't get the INDIRECT function to bring up the sheet name..?!? I think I'm copying the explanation exactly that you sent the link for - I've started from a blank workbook, and I'm using Ctrl+Shift+Enter...

{=INDEX(MySheets, MATCH(1, COUNTIF(INDIRECT("'" & MySheets & "'!A1:A50"), A3), 0))}

Any idea what I could be doing wrong - I'm using 2003...

Scratch that - I'd somehow defined MySheets twice - it works - it's a MEEERACLE!!
Gawd Bless you Dom - magical!
JP

Dom,
Decided to heed you advice and instead of having 52 (+8) worksheets I now have 9.
Managed to script the cells to repeat the same table (with a varying date for lookup purposes) every 100 rows, which is easy enought to expand to every 200 / 1000 / n rows....
However I now find that my workbook has ballooned from a podgy 7.5MB to a rather unhealthy 13.3MB!
I even managed to trim down all of my longer formulae (the INDEX / MATCH / INDIRECT CTRL+SHIFT+ENTER ones that scanned all 52 sheets) to a much tidier VLOOKUP C:C option....

I've experienced this in the past and found that I had a load of apparently blank (but not-blank) cells which were like some kind of file-size expanding anti-matter, a collegue calls these duffs and wrote the following code to erase them...

Sub RemoveDuffs()
Dim LastRow As Long, LastCol As Integer, cell As Range

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

For Each cell In Range(Cells(1, 1), Cells(LastRow, LastCol))
If IsEmpty(cell) = False And Len(cell) = 0 Then cell.Clear
Next cell

End Sub

I've tried that but still the file is a whopper!
Could it be the mutiple references across the sheets causing this Excel obesity...?!

JP