Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula reference between worksheets | Excel Worksheet Functions | |||
Autofill Match Formula on different worksheets | Excel Worksheet Functions | |||
reference autofill. | Excel Worksheet Functions | |||
How do I copy a reference formula onto multiple worksheets | Excel Worksheet Functions | |||
Autofill/Reference Confusion | Excel Worksheet Functions |