How to reference different worksheets in an autofill formula


C

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!
 
Ad

Advertisements

C

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

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

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.
 
G

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
 
Ad

Advertisements

Ad

Advertisements

C

csdjj

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

Gord Dibben said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top