Vlookup for multiple worksheets

I have about 9 tables of data in separate worksheets that I'd like to collate into one table which contains all the data and updates automatically when new data is entered into the original tables.

My plan was to use a vlookup function and have a column telling the function which sheet to look in; something like this:

=vlookup(A1, B1! C1:Z100,2,true)

Where A1 is the ref number of the row of data, B1 contains the name of the source spreadsheet, and cells C1-Z100 in that source spreadsheet contain the data I'm looking for.

However, I can't get Excel to recognise a sheet name created using a formula. Is there a way to do this?

Failing that, is there another way to do what I'm trying to achieve?

Thanks,

Tim

Thanks, that's fantastic! Although I'm really not sure how it works.

A couple of other minor things I can't figure out:
- I'd like to get blank cells (rather than zeros) where the source cell is blank. Is there a tidier way to do this than a nested IF with the above function twice?
- is it possible to get each value to have the same formatting as the source cell?

The data being looked up is numeric, except for 1 or 2 columns. The reason I'd like to get blank cells is so that I can average the data in the main table.

No, all the results are positive and non-zero.