Left out some info:

Then your lookup formula becomes:
=VLOOKUP(D$7,INDIRECT("'"&A1&"'!A:J"),5,0)

Then you can copy down as needed and reference each sheet with the same
formula.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
If your sheet names followed a sequential pattern like Client 1, Client 2,
Client 3 then you could do what you want *without* having to use a table.
So, you'll have to make a list of the sheet names:

A1 = Microsoft
A2 = Crate and Barrel

Then your lookup formula becomes:

=VLOOKUP(D$7,INDIRECT("'"&A1&"'!A:J"),5,0)

--
Biff
Microsoft Excel MVP


"csdjj" wrote in message
...
No -- each sheet is named with the client's name. If you're asking
whether
the sheets are one or two-word names, it differs. Some names (like
Microsoft) are one-word names and some (like Crate and Barrel) are two
words.

I can make adjustments to the names, though, if that will help.

"T. Valko" wrote:

Are your real sheet names Client 1, Client 2, Client 3?

--
Biff
Microsoft Excel MVP


"csdjj" wrote in message
...
I am working in Excel 2002. I have a situation where I have 30+
separate
worksheets (one per client). Each worksheet is the exact same
template --
i.e., all of the formulas are in the exact same cells in each
worksheet.

I want to create a 'summary' worksheet which references the same cells
from
each of the client worksheets. For example: =VLOOKUP(D7,'Client
1'!A:J,5,FALSE).

Each row in the summary sheet would represent a different client. So,
row
one would be =VLOOKUP(D7,'Client 1'!A:J,5,FALSE), row 2 would be
=VLOOKUP(D7,'Client 2'!A:J,5,FALSE), etc.

Is there an easy way for me to change the worksheet each formula is
referencing (by using a lookup table of some sort) rather than having
to
open
each formula and replace the old worksheet reference with the new
worksheet
reference?

Any help would be greatly appreciated!

Thanks!