Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Next Sheet and Every Other Cell
I have a workbook with multiple sheets. I would like to link each sheet to a
Summary sheet. The tricky part is that I would like to enter the formula one time and then copy as there are about 10 columns across and 200 rows down. The formula I have is: ='Sheet Name'!Cell Reference I need the sheet name in the formula to increment to the next sheet in the workbook when copied down the 200 rows with the cell reference remaining constant For Example: Row 1 ='Sheet Name+1'!Cell Reference Row 2 ='Sheet Name+2'!Cell Reference I also need the cell reference to increment by 2 (every other) and the sheet name to remain constant when copied across the 10 rows. For Example: Column A Column B ='Sheet Name'!Cell Reference ='Sheet Name'!Cell Reference +2 -- JEverhart |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Next Sheet and Every Other Cell
Okay, so what's the first cell reference you will have?
Pete On May 16, 8:44*pm, JEverhart wrote: I have a workbook with multiple sheets. *I would like to link each sheet to a Summary sheet. *The tricky part is that I would like to enter the formula one time and then copy as there are about 10 columns across and 200 rows down. * The formula I have is: * * *='Sheet Name'!Cell Reference I need the sheet name in the formula to increment to the next sheet in the workbook when copied down the 200 rows with the cell reference remaining constant * * *For Example: Row 1 * * * * *='Sheet Name+1'!Cell Reference Row 2 * * * * *='Sheet Name+2'!Cell Reference I also need the cell reference to increment by 2 (every other) and the sheet name to remain constant when copied across the 10 rows. * * *For Example: * * * * * * *Column A * * * * * * * * * * * * * * * * *Column B * * * * * ='Sheet Name'!Cell Reference * * * ='Sheet Name'!Cell Reference +2 * -- JEverhart |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Next Sheet and Every Other Cell
Ah! I thought from your first posting that the sheet names would have
a number in them that could be incremented, like Sheet1, Sheet2 etc. I can't see how you can do that now if you first sheet name is: A. Vision Mission Values Basically, though, to do what you want you would have to use the INDIRECT function - this allows you to build up sheet and cell references as if they were strings. You can make use of the ROW and COLUMN functions to increment the cell references, something like: =INDIRECT("'Sheet"&ROW(A1)&"'!D48") This will give you the first thing you asked for, and you could just copy this into the 9 other columns on that row and manually change D48 to F48, H48, J48 etc, before copying the formula down your 200 rows. If you wanted the D to change automatically to the next letter but two each time, then you could amend the formula to this: =INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48") Then you could copy this across 9 columns and then copy these 10 columns down. However, you would need to change your sheet names for this to work. Pete On May 16, 10:30*pm, JEverhart wrote: The first sheet name is 'A. *Vision Mission Values' The first cell reference is D48 on the above named sheet. So my first formula is ='A. *Vision MIssion Values'!D48. What can I do to this formula to cause the 'Sheet Name' to progress to the next sheet in the same workbook and and also cause the cell reference to skip E48 and go to F48? -- JEverhart "Pete_UK" wrote: Okay, so what's the first cell reference you will have? Pete On May 16, 8:44 pm, JEverhart wrote: I have a workbook with multiple sheets. *I would like to link each sheet to a Summary sheet. *The tricky part is that I would like to enter the formula one time and then copy as there are about 10 columns across and 200 rows down. * The formula I have is: * * *='Sheet Name'!Cell Reference I need the sheet name in the formula to increment to the next sheet in the workbook when copied down the 200 rows with the cell reference remaining constant * * *For Example: Row 1 * * * * *='Sheet Name+1'!Cell Reference Row 2 * * * * *='Sheet Name+2'!Cell Reference I also need the cell reference to increment by 2 (every other) and the sheet name to remain constant when copied across the 10 rows. * * *For Example: * * * * * * *Column A * * * * * * * * * * * * * * * * *Column B * * * * * ='Sheet Name'!Cell Reference * * * ='Sheet Name'!Cell Reference +2 * -- JEverhart- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Next Sheet and Every Other Cell
Thinking further on this, you could avoid having to rename all your
sheets if you were to list them somewhere in your summary sheet (for example in cells X1:X200). Then you could use this formula in your first cell: =INDIRECT("'"&INDEX($X$1:$X $200,ROW(A1))&"'!"&CHAR(COLUMN(A1)*2+66)&"48") Note the apostrophes - between the first set of quotes and immediately before the exclamation mark. These will allow the use of spaces in your names. Your sheet names in X1:X200 must be typed exactly as they appear on the tabs, including any leading or trailing spaces. Now you can copy this across and down as required. Hope this helps. Pete On May 17, 2:50*am, Pete_UK wrote: Ah! *I thought from your first posting that the sheet names would have a number in them that could be incremented, like Sheet1, Sheet2 etc. I can't see how you can do that now if you first sheet name is: A. *Vision Mission Values Basically, though, to do what you want you would have to use the INDIRECT function - this allows you to build up sheet and cell references as if they were strings. You can make use of the ROW and COLUMN functions to increment the cell references, something like: =INDIRECT("'Sheet"&ROW(A1)&"'!D48") This will give you the first thing you asked for, and you could just copy this into the 9 other columns on that row and manually change D48 to F48, H48, J48 etc, before copying the formula down your 200 rows. If you wanted the D to change automatically to the next letter but two each time, then you could amend the formula to this: =INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48") Then you could copy this across 9 columns and then copy these 10 columns down. However, you would need to change your sheet names for this to work. Pete |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Next Sheet and Every Other Cell
If you go with Pete's suggestion, here is macro to get the list of sheet names
into column X Sub CreateListOfSheetsOnFirstSheet() Dim ws As Worksheet For i = 1 To Worksheets.Count With Worksheets(1) Set ws = Worksheets(i) .Cells(i, 24).Value = ws.Name End With Next i End Sub Gord Dibben MS Excel MVP On Sat, 17 May 2008 05:20:50 -0700 (PDT), Pete_UK wrote: Thinking further on this, you could avoid having to rename all your sheets if you were to list them somewhere in your summary sheet (for example in cells X1:X200). Then you could use this formula in your first cell: =INDIRECT("'"&INDEX($X$1:$X $200,ROW(A1))&"'!"&CHAR(COLUMN(A1)*2+66)&"48") Note the apostrophes - between the first set of quotes and immediately before the exclamation mark. These will allow the use of spaces in your names. Your sheet names in X1:X200 must be typed exactly as they appear on the tabs, including any leading or trailing spaces. Now you can copy this across and down as required. Hope this helps. Pete On May 17, 2:50*am, Pete_UK wrote: Ah! *I thought from your first posting that the sheet names would have a number in them that could be incremented, like Sheet1, Sheet2 etc. I can't see how you can do that now if you first sheet name is: A. *Vision Mission Values Basically, though, to do what you want you would have to use the INDIRECT function - this allows you to build up sheet and cell references as if they were strings. You can make use of the ROW and COLUMN functions to increment the cell references, something like: =INDIRECT("'Sheet"&ROW(A1)&"'!D48") This will give you the first thing you asked for, and you could just copy this into the 9 other columns on that row and manually change D48 to F48, H48, J48 etc, before copying the formula down your 200 rows. If you wanted the D to change automatically to the next letter but two each time, then you could amend the formula to this: =INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48") Then you could copy this across 9 columns and then copy these 10 columns down. However, you would need to change your sheet names for this to work. Pete |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Next Sheet and Every Other Cell
Thanks for that, Gord - quicker than typing 200 names !! <bg
Pete On May 17, 5:30*pm, Gord Dibben <gorddibbATshawDOTca wrote: If you go with Pete's suggestion, here is macro to get the list of sheet names into column X Sub CreateListOfSheetsOnFirstSheet() * * Dim ws As Worksheet * * For i = 1 To Worksheets.Count * * * * With Worksheets(1) * * * * * * Set ws = Worksheets(i) * * * * * * .Cells(i, 24).Value = ws.Name * * * * End With * * Next i End Sub Gord Dibben *MS Excel MVP On Sat, 17 May 2008 05:20:50 -0700 (PDT), Pete_UK wrote: Thinking further on this, you could avoid having to rename all your sheets if you were to list them somewhere in your summary sheet (for example in cells X1:X200). Then you could use this formula in your first cell: =INDIRECT("'"&INDEX($X$1:$X $200,ROW(A1))&"'!"&CHAR(COLUMN(A1)*2+66)&"48") Note the apostrophes - between the first set of quotes and immediately before the exclamation mark. These will allow the use of spaces in your names. Your sheet names in X1:X200 must be typed exactly as they appear on the tabs, including any leading or trailing spaces. Now you can copy this across and down as required. Hope this helps. Pete On May 17, 2:50*am, Pete_UK wrote: Ah! *I thought from your first posting that the sheet names would have a number in them that could be incremented, like Sheet1, Sheet2 etc. I can't see how you can do that now if you first sheet name is: A. *Vision Mission Values Basically, though, to do what you want you would have to use the INDIRECT function - this allows you to build up sheet and cell references as if they were strings. You can make use of the ROW and COLUMN functions to increment the cell references, something like: =INDIRECT("'Sheet"&ROW(A1)&"'!D48") This will give you the first thing you asked for, and you could just copy this into the 9 other columns on that row and manually change D48 to F48, H48, J48 etc, before copying the formula down your 200 rows. If you wanted the D to change automatically to the next letter but two each time, then you could amend the formula to this: =INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48") Then you could copy this across 9 columns and then copy these 10 columns down. However, you would need to change your sheet names for this to work. Pete- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Next Sheet and Every Other Cell
Every little bit helps Pete.
I'm lazy so found the macro excellent for this type of operation when sheetnames are not standard. Gord On Sat, 17 May 2008 10:58:01 -0700 (PDT), Pete_UK wrote: Thanks for that, Gord - quicker than typing 200 names !! <bg Pete On May 17, 5:30*pm, Gord Dibben <gorddibbATshawDOTca wrote: If you go with Pete's suggestion, here is macro to get the list of sheet names into column X Sub CreateListOfSheetsOnFirstSheet() * * Dim ws As Worksheet * * For i = 1 To Worksheets.Count * * * * With Worksheets(1) * * * * * * Set ws = Worksheets(i) * * * * * * .Cells(i, 24).Value = ws.Name * * * * End With * * Next i End Sub Gord Dibben *MS Excel MVP On Sat, 17 May 2008 05:20:50 -0700 (PDT), Pete_UK wrote: Thinking further on this, you could avoid having to rename all your sheets if you were to list them somewhere in your summary sheet (for example in cells X1:X200). Then you could use this formula in your first cell: =INDIRECT("'"&INDEX($X$1:$X $200,ROW(A1))&"'!"&CHAR(COLUMN(A1)*2+66)&"48") Note the apostrophes - between the first set of quotes and immediately before the exclamation mark. These will allow the use of spaces in your names. Your sheet names in X1:X200 must be typed exactly as they appear on the tabs, including any leading or trailing spaces. Now you can copy this across and down as required. Hope this helps. Pete On May 17, 2:50*am, Pete_UK wrote: Ah! *I thought from your first posting that the sheet names would have a number in them that could be incremented, like Sheet1, Sheet2 etc. I can't see how you can do that now if you first sheet name is: A. *Vision Mission Values Basically, though, to do what you want you would have to use the INDIRECT function - this allows you to build up sheet and cell references as if they were strings. You can make use of the ROW and COLUMN functions to increment the cell references, something like: =INDIRECT("'Sheet"&ROW(A1)&"'!D48") This will give you the first thing you asked for, and you could just copy this into the 9 other columns on that row and manually change D48 to F48, H48, J48 etc, before copying the formula down your 200 rows. If you wanted the D to change automatically to the next letter but two each time, then you could amend the formula to this: =INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48") Then you could copy this across 9 columns and then copy these 10 columns down. However, you would need to change your sheet names for this to work. Pete- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Next Sheet and Every Other Cell
WOW....I can't believe I found 'almost' the help I needed exactly!
PETE (or anyone else who knows this one), how would I do this if I have the same problem AND my sheet names are numbers. ex. The formula I want to copy is: '1'!H3 When I copy it, I would like the sheet number to increment and the cell number to remain fixed. The pasted result should be (in my perfect world): '2'!H3 I've searched and searched, and had no luck. I'm grateful for any help that may be out there... Cheers Sandy "Pete_UK" wrote: Ah! I thought from your first posting that the sheet names would have a number in them that could be incremented, like Sheet1, Sheet2 etc. I can't see how you can do that now if you first sheet name is: A. Vision Mission Values Basically, though, to do what you want you would have to use the INDIRECT function - this allows you to build up sheet and cell references as if they were strings. You can make use of the ROW and COLUMN functions to increment the cell references, something like: =INDIRECT("'Sheet"&ROW(A1)&"'!D48") This will give you the first thing you asked for, and you could just copy this into the 9 other columns on that row and manually change D48 to F48, H48, J48 etc, before copying the formula down your 200 rows. If you wanted the D to change automatically to the next letter but two each time, then you could amend the formula to this: =INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48") Then you could copy this across 9 columns and then copy these 10 columns down. However, you would need to change your sheet names for this to work. Pete On May 16, 10:30 pm, JEverhart wrote: The first sheet name is 'A. Vision Mission Values' The first cell reference is D48 on the above named sheet. So my first formula is ='A. Vision MIssion Values'!D48. What can I do to this formula to cause the 'Sheet Name' to progress to the next sheet in the same workbook and and also cause the cell reference to skip E48 and go to F48? -- JEverhart "Pete_UK" wrote: Okay, so what's the first cell reference you will have? Pete On May 16, 8:44 pm, JEverhart wrote: I have a workbook with multiple sheets. I would like to link each sheet to a Summary sheet. The tricky part is that I would like to enter the formula one time and then copy as there are about 10 columns across and 200 rows down. The formula I have is: ='Sheet Name'!Cell Reference I need the sheet name in the formula to increment to the next sheet in the workbook when copied down the 200 rows with the cell reference remaining constant For Example: Row 1 ='Sheet Name+1'!Cell Reference Row 2 ='Sheet Name+2'!Cell Reference I also need the cell reference to increment by 2 (every other) and the sheet name to remain constant when copied across the 10 rows. For Example: Column A Column B ='Sheet Name'!Cell Reference ='Sheet Name'!Cell Reference +2 -- JEverhart- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Next Sheet and Every Other Cell
=INDIRECT(ROW(A1)&"!H3")
Gord Dibben MS Excel MVP On Thu, 17 Jul 2008 12:53:15 -0700, misteremanca wrote: WOW....I can't believe I found 'almost' the help I needed exactly! PETE (or anyone else who knows this one), how would I do this if I have the same problem AND my sheet names are numbers. ex. The formula I want to copy is: '1'!H3 When I copy it, I would like the sheet number to increment and the cell number to remain fixed. The pasted result should be (in my perfect world): '2'!H3 I've searched and searched, and had no luck. I'm grateful for any help that may be out there... Cheers Sandy "Pete_UK" wrote: Ah! I thought from your first posting that the sheet names would have a number in them that could be incremented, like Sheet1, Sheet2 etc. I can't see how you can do that now if you first sheet name is: A. Vision Mission Values Basically, though, to do what you want you would have to use the INDIRECT function - this allows you to build up sheet and cell references as if they were strings. You can make use of the ROW and COLUMN functions to increment the cell references, something like: =INDIRECT("'Sheet"&ROW(A1)&"'!D48") This will give you the first thing you asked for, and you could just copy this into the 9 other columns on that row and manually change D48 to F48, H48, J48 etc, before copying the formula down your 200 rows. If you wanted the D to change automatically to the next letter but two each time, then you could amend the formula to this: =INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48") Then you could copy this across 9 columns and then copy these 10 columns down. However, you would need to change your sheet names for this to work. Pete On May 16, 10:30 pm, JEverhart wrote: The first sheet name is 'A. Vision Mission Values' The first cell reference is D48 on the above named sheet. So my first formula is ='A. Vision MIssion Values'!D48. What can I do to this formula to cause the 'Sheet Name' to progress to the next sheet in the same workbook and and also cause the cell reference to skip E48 and go to F48? -- JEverhart "Pete_UK" wrote: Okay, so what's the first cell reference you will have? Pete On May 16, 8:44 pm, JEverhart wrote: I have a workbook with multiple sheets. I would like to link each sheet to a Summary sheet. The tricky part is that I would like to enter the formula one time and then copy as there are about 10 columns across and 200 rows down. The formula I have is: ='Sheet Name'!Cell Reference I need the sheet name in the formula to increment to the next sheet in the workbook when copied down the 200 rows with the cell reference remaining constant For Example: Row 1 ='Sheet Name+1'!Cell Reference Row 2 ='Sheet Name+2'!Cell Reference I also need the cell reference to increment by 2 (every other) and the sheet name to remain constant when copied across the 10 rows. For Example: Column A Column B ='Sheet Name'!Cell Reference ='Sheet Name'!Cell Reference +2 -- JEverhart- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Next Sheet and Every Other Cell
Thanks VERY MUCH for the reply Gord. It worked like a charm.
I've done some searching since, on the INDIRECT function, and I can't seem to figure out what the A refers to. Could I have used ANY letter there? Is there a site you could point me to that explains INDIRECT well? Cheers Alexander "Gord Dibben" wrote: =INDIRECT(ROW(A1)&"!H3") Gord Dibben MS Excel MVP |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Next Sheet and Every Other Cell
ROW(A1) means the row number of A1 which is 1.........the name of your
worksheet 1 giving you the same as typing in =1!$H$3 As you drag the formula down the A1 will change to A2 but the H3 will not change because it is enclosed in quotes So in row 2 you will get =INDIRECT(ROW(A2)&"!H3" giving you same as typing in =2!$H$3 The A is just a column letter Yes.......you could change the column to any column letter. i.e. =INDIRECT(ROW(Q1)&"!H3" You could also use =INDIRECT(ROW(1:1)&"!H3" and drag that down. See Debra Dalgleish's site for more on INDIRECT http://www.contextures.on.ca/xlFunctions05.html Gord On Thu, 17 Jul 2008 15:24:08 -0700, misteremanca wrote: Thanks VERY MUCH for the reply Gord. It worked like a charm. I've done some searching since, on the INDIRECT function, and I can't seem to figure out what the A refers to. Could I have used ANY letter there? Is there a site you could point me to that explains INDIRECT well? Cheers Alexander "Gord Dibben" wrote: =INDIRECT(ROW(A1)&"!H3") Gord Dibben MS Excel MVP |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Next Sheet and Every Other Cell
Thanks very much Gord! I've got it.
The word 'row' threw me off.....but I'm understanding it now. Cheers eman "Gord Dibben" wrote: ROW(A1) means the row number of A1 which is 1.........the name of your worksheet 1 giving you the same as typing in =1!$H$3 As you drag the formula down the A1 will change to A2 but the H3 will not change because it is enclosed in quotes So in row 2 you will get =INDIRECT(ROW(A2)&"!H3" giving you same as typing in =2!$H$3 The A is just a column letter Yes.......you could change the column to any column letter. i.e. =INDIRECT(ROW(Q1)&"!H3" You could also use =INDIRECT(ROW(1:1)&"!H3" and drag that down. See Debra Dalgleish's site for more on INDIRECT http://www.contextures.on.ca/xlFunctions05.html Gord On Thu, 17 Jul 2008 15:24:08 -0700, misteremanca wrote: Thanks VERY MUCH for the reply Gord. It worked like a charm. I've done some searching since, on the INDIRECT function, and I can't seem to figure out what the A refers to. Could I have used ANY letter there? Is there a site you could point me to that explains INDIRECT well? Cheers Alexander |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I annotate a formula result in Sheet 1 in Sheet 2 by Cell Addr | Excel Worksheet Functions | |||
FORMULA COPY SHEET IN THE OTHER CELL | Excel Worksheet Functions | |||
how to copy a cell with formula from sheet 1 (data is all vertical) into sheet 2 | Excel Worksheet Functions | |||
Lookup cell contents in on sheet based on a formula in second sheet | Excel Worksheet Functions | |||
Is there a formula that will place the sheet name in a cell? | Excel Worksheet Functions |