How to reference different worksheets in an autofill formula

[csdjj]

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!

 

[T. Valko]

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

 

[csdjj]

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]

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)

 

[T. Valko]

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.

 

[Gord Dibben]

An easy way to get all the sheet names in a list is to run this macro.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "List"
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub


Gord Dibben MS Excel MVP

 

[csdjj]

Works great!! Thanks!!

Left out some info:


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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[csdjj]

Wow -- that's VERY cool! Thanks!!

An easy way to get all the sheet names in a list is to run this macro.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "List"
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub


Gord Dibben MS Excel MVP