How to reference different worksheets in an autofill formula
 

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!

How to reference different worksheets in an autofill formula
 

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!

How to reference different worksheets in an autofill formula
 

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!

How to reference different worksheets in an autofill formula
 

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!

How to reference different worksheets in an autofill formula
 

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!

How to reference different worksheets in an autofill formula
 

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

On Fri, 1 Feb 2008 13:42:02 -0500, "T. Valko" wrote:

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)

How to reference different worksheets in an autofill formula
 

Works great!! Thanks!!

"T. Valko" wrote:

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!

How to reference different worksheets in an autofill formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"csdjj" wrote in message
...
Works great!! Thanks!!

"T. Valko" wrote:

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!

How to reference different worksheets in an autofill formula
 

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

"Gord Dibben" wrote:

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

On Fri, 1 Feb 2008 13:42:02 -0500, "T. Valko" wrote:

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)