|
Edit Multiple Cells
I need to select multipe cells and change just the reference page for the
selected cells, not the other reference information in the cell. Find and Replace does not work. Example, this are four cell, I want to just change the 5 (which is the refence page). ='5'!C36, ='5'!C377, ='5'!C38, ='5'!C39 Or, I am trying to create a spreadsheet for example January's information will have 10 rows that reference 10 different organization that are referenced from Sheets 1 through 10 (sheet 1 is organization #1, etc...) When I try to use the autofill function, instead of changing the the reference page number it will increase the cell number (autofill down) or autofill to the right will increase the column name but not change the page or cell number. What I really need to be able to do is autofill down keeping the same column and cell but increasing the page reference. Any help would be appreciated. Thanks in advance. |
Edit Multiple Cells
Jenn
For the first question try EditReplace What: '5!' With: '6'! Replace all. For second question, use the INDIRECT function to fill down or across =INDIRECT("'organization #" & (ROW()) & '"!B12") entered in A1 and copied down. To copy across, change ROW() to COLUMN() Gord Dibben MS Excel MVP On Sun, 13 Jan 2008 13:59:00 -0800, Jenn wrote: I need to select multipe cells and change just the reference page for the selected cells, not the other reference information in the cell. Find and Replace does not work. Example, this are four cell, I want to just change the 5 (which is the refence page). ='5'!C36, ='5'!C377, ='5'!C38, ='5'!C39 Or, I am trying to create a spreadsheet for example January's information will have 10 rows that reference 10 different organization that are referenced from Sheets 1 through 10 (sheet 1 is organization #1, etc...) When I try to use the autofill function, instead of changing the the reference page number it will increase the cell number (autofill down) or autofill to the right will increase the column name but not change the page or cell number. What I really need to be able to do is autofill down keeping the same column and cell but increasing the page reference. Any help would be appreciated. Thanks in advance. |
Edit Multiple Cells
I can't seem to get the indirect function to work
"Gord Dibben" wrote: Jenn For the first question try EditReplace What: '5!' With: '6'! Replace all. For second question, use the INDIRECT function to fill down or across =INDIRECT("'organization #" & (ROW()) & '"!B12") entered in A1 and copied down. To copy across, change ROW() to COLUMN() Gord Dibben MS Excel MVP On Sun, 13 Jan 2008 13:59:00 -0800, Jenn wrote: I need to select multipe cells and change just the reference page for the selected cells, not the other reference information in the cell. Find and Replace does not work. Example, this are four cell, I want to just change the 5 (which is the refence page). ='5'!C36, ='5'!C377, ='5'!C38, ='5'!C39 Or, I am trying to create a spreadsheet for example January's information will have 10 rows that reference 10 different organization that are referenced from Sheets 1 through 10 (sheet 1 is organization #1, etc...) When I try to use the autofill function, instead of changing the the reference page number it will increase the cell number (autofill down) or autofill to the right will increase the column name but not change the page or cell number. What I really need to be able to do is autofill down keeping the same column and cell but increasing the page reference. Any help would be appreciated. Thanks in advance. |
Edit Multiple Cells
lets see if I put this in correct
=INDIRECT("'1" & (ROW(31) & '"!C31") Organization # is Sheet 1 the Row Number in Sheet 1 is 31 and the Cell # is C31 "Gord Dibben" wrote: Jenn For the first question try EditReplace What: '5!' With: '6'! Replace all. For second question, use the INDIRECT function to fill down or across =INDIRECT("'organization #" & (ROW()) & '"!B12") entered in A1 and copied down. To copy across, change ROW() to COLUMN() Gord Dibben MS Excel MVP On Sun, 13 Jan 2008 13:59:00 -0800, Jenn wrote: I need to select multipe cells and change just the reference page for the selected cells, not the other reference information in the cell. Find and Replace does not work. Example, this are four cell, I want to just change the 5 (which is the refence page). ='5'!C36, ='5'!C377, ='5'!C38, ='5'!C39 Or, I am trying to create a spreadsheet for example January's information will have 10 rows that reference 10 different organization that are referenced from Sheets 1 through 10 (sheet 1 is organization #1, etc...) When I try to use the autofill function, instead of changing the the reference page number it will increase the cell number (autofill down) or autofill to the right will increase the column name but not change the page or cell number. What I really need to be able to do is autofill down keeping the same column and cell but increasing the page reference. Any help would be appreciated. Thanks in advance. |
Edit Multiple Cells
the Edit Replace does not work either, says it can not find data
"Gord Dibben" wrote: Jenn For the first question try EditReplace What: '5!' With: '6'! Replace all. For second question, use the INDIRECT function to fill down or across =INDIRECT("'organization #" & (ROW()) & '"!B12") entered in A1 and copied down. To copy across, change ROW() to COLUMN() Gord Dibben MS Excel MVP On Sun, 13 Jan 2008 13:59:00 -0800, Jenn wrote: I need to select multipe cells and change just the reference page for the selected cells, not the other reference information in the cell. Find and Replace does not work. Example, this are four cell, I want to just change the 5 (which is the refence page). ='5'!C36, ='5'!C377, ='5'!C38, ='5'!C39 Or, I am trying to create a spreadsheet for example January's information will have 10 rows that reference 10 different organization that are referenced from Sheets 1 through 10 (sheet 1 is organization #1, etc...) When I try to use the autofill function, instead of changing the the reference page number it will increase the cell number (autofill down) or autofill to the right will increase the column name but not change the page or cell number. What I really need to be able to do is autofill down keeping the same column and cell but increasing the page reference. Any help would be appreciated. Thanks in advance. |
Edit Multiple Cells
I hope you didn't use my example because there was a typo in it.
'5'! not '5!' Also, in Options do you have "look in"Formulas checked? Gord Sun, 13 Jan 2008 17:27:02 -0800, Jenn wrote: the Edit Replace does not work either, says it can not find data "Gord Dibben" wrote: Jenn For the first question try EditReplace What: '5!' With: '6'! Replace all. For second question, use the INDIRECT function to fill down or across =INDIRECT("'organization #" & (ROW()) & '"!B12") entered in A1 and copied down. To copy across, change ROW() to COLUMN() Gord Dibben MS Excel MVP On Sun, 13 Jan 2008 13:59:00 -0800, Jenn wrote: I need to select multipe cells and change just the reference page for the selected cells, not the other reference information in the cell. Find and Replace does not work. Example, this are four cell, I want to just change the 5 (which is the refence page). ='5'!C36, ='5'!C377, ='5'!C38, ='5'!C39 Or, I am trying to create a spreadsheet for example January's information will have 10 rows that reference 10 different organization that are referenced from Sheets 1 through 10 (sheet 1 is organization #1, etc...) When I try to use the autofill function, instead of changing the the reference page number it will increase the cell number (autofill down) or autofill to the right will increase the column name but not change the page or cell number. What I really need to be able to do is autofill down keeping the same column and cell but increasing the page reference. Any help would be appreciated. Thanks in advance. |
Edit Multiple Cells
Post a sample of your actual sheet names please.
I am confused. Your first post said they were "organization #1 etc." Where will you be entering the first formula to fill down? Which sheet and what cell? Gord On Sun, 13 Jan 2008 16:51:11 -0800, Jenn wrote: lets see if I put this in correct =INDIRECT("'1" & (ROW(31) & '"!C31") Organization # is Sheet 1 the Row Number in Sheet 1 is 31 and the Cell # is C31 "Gord Dibben" wrote: Jenn For the first question try EditReplace What: '5!' With: '6'! Replace all. For second question, use the INDIRECT function to fill down or across =INDIRECT("'organization #" & (ROW()) & '"!B12") entered in A1 and copied down. To copy across, change ROW() to COLUMN() Gord Dibben MS Excel MVP On Sun, 13 Jan 2008 13:59:00 -0800, Jenn wrote: I need to select multipe cells and change just the reference page for the selected cells, not the other reference information in the cell. Find and Replace does not work. Example, this are four cell, I want to just change the 5 (which is the refence page). ='5'!C36, ='5'!C377, ='5'!C38, ='5'!C39 Or, I am trying to create a spreadsheet for example January's information will have 10 rows that reference 10 different organization that are referenced from Sheets 1 through 10 (sheet 1 is organization #1, etc...) When I try to use the autofill function, instead of changing the the reference page number it will increase the cell number (autofill down) or autofill to the right will increase the column name but not change the page or cell number. What I really need to be able to do is autofill down keeping the same column and cell but increasing the page reference. Any help would be appreciated. Thanks in advance. |
Edit Multiple Cells
yes the formulas options is selected
I tried several variations of searching, nothing worked. "Gord Dibben" wrote: I hope you didn't use my example because there was a typo in it. '5'! not '5!' Also, in Options do you have "look in"Formulas checked? Gord Sun, 13 Jan 2008 17:27:02 -0800, Jenn wrote: the Edit Replace does not work either, says it can not find data "Gord Dibben" wrote: Jenn For the first question try EditReplace What: '5!' With: '6'! Replace all. For second question, use the INDIRECT function to fill down or across =INDIRECT("'organization #" & (ROW()) & '"!B12") entered in A1 and copied down. To copy across, change ROW() to COLUMN() Gord Dibben MS Excel MVP On Sun, 13 Jan 2008 13:59:00 -0800, Jenn wrote: I need to select multipe cells and change just the reference page for the selected cells, not the other reference information in the cell. Find and Replace does not work. Example, this are four cell, I want to just change the 5 (which is the refence page). ='5'!C36, ='5'!C377, ='5'!C38, ='5'!C39 Or, I am trying to create a spreadsheet for example January's information will have 10 rows that reference 10 different organization that are referenced from Sheets 1 through 10 (sheet 1 is organization #1, etc...) When I try to use the autofill function, instead of changing the the reference page number it will increase the cell number (autofill down) or autofill to the right will increase the column name but not change the page or cell number. What I really need to be able to do is autofill down keeping the same column and cell but increasing the page reference. Any help would be appreciated. Thanks in advance. |
Edit Multiple Cells
The workbook has 12 sheets for each month they are name January, February,
etc... The organization sheets are names simply 1,2,3,4,5 etc..... I would start the fill down on the January sheet, C6 Row 6 contains info from organization 1 column C Row 7 contain info from organization 2 column C etc.. Thanks again for your help "Gord Dibben" wrote: Post a sample of your actual sheet names please. I am confused. Your first post said they were "organization #1 etc." Where will you be entering the first formula to fill down? Which sheet and what cell? Gord On Sun, 13 Jan 2008 16:51:11 -0800, Jenn wrote: lets see if I put this in correct =INDIRECT("'1" & (ROW(31) & '"!C31") Organization # is Sheet 1 the Row Number in Sheet 1 is 31 and the Cell # is C31 "Gord Dibben" wrote: Jenn For the first question try EditReplace What: '5!' With: '6'! Replace all. For second question, use the INDIRECT function to fill down or across =INDIRECT("'organization #" & (ROW()) & '"!B12") entered in A1 and copied down. To copy across, change ROW() to COLUMN() Gord Dibben MS Excel MVP On Sun, 13 Jan 2008 13:59:00 -0800, Jenn wrote: I need to select multipe cells and change just the reference page for the selected cells, not the other reference information in the cell. Find and Replace does not work. Example, this are four cell, I want to just change the 5 (which is the refence page). ='5'!C36, ='5'!C377, ='5'!C38, ='5'!C39 Or, I am trying to create a spreadsheet for example January's information will have 10 rows that reference 10 different organization that are referenced from Sheets 1 through 10 (sheet 1 is organization #1, etc...) When I try to use the autofill function, instead of changing the the reference page number it will increase the cell number (autofill down) or autofill to the right will increase the column name but not change the page or cell number. What I really need to be able to do is autofill down keeping the same column and cell but increasing the page reference. Any help would be appreciated. Thanks in advance. |
Edit Multiple Cells
In January sheet in C6 enter =INDIRECT(ROW(1:1)&"!C10") and copy down etc
number of rows. Will return the C10 values of sheets 1,2,3,4,5 etc. Gord On Mon, 14 Jan 2008 17:28:01 -0800, Jenn wrote: The workbook has 12 sheets for each month they are name January, February, etc... The organization sheets are names simply 1,2,3,4,5 etc..... I would start the fill down on the January sheet, C6 Row 6 contains info from organization 1 column C Row 7 contain info from organization 2 column C etc.. Thanks again for your help "Gord Dibben" wrote: Post a sample of your actual sheet names please. I am confused. Your first post said they were "organization #1 etc." Where will you be entering the first formula to fill down? Which sheet and what cell? Gord On Sun, 13 Jan 2008 16:51:11 -0800, Jenn wrote: lets see if I put this in correct =INDIRECT("'1" & (ROW(31) & '"!C31") Organization # is Sheet 1 the Row Number in Sheet 1 is 31 and the Cell # is C31 "Gord Dibben" wrote: Jenn For the first question try EditReplace What: '5!' With: '6'! Replace all. For second question, use the INDIRECT function to fill down or across =INDIRECT("'organization #" & (ROW()) & '"!B12") entered in A1 and copied down. To copy across, change ROW() to COLUMN() Gord Dibben MS Excel MVP On Sun, 13 Jan 2008 13:59:00 -0800, Jenn wrote: I need to select multipe cells and change just the reference page for the selected cells, not the other reference information in the cell. Find and Replace does not work. Example, this are four cell, I want to just change the 5 (which is the refence page). ='5'!C36, ='5'!C377, ='5'!C38, ='5'!C39 Or, I am trying to create a spreadsheet for example January's information will have 10 rows that reference 10 different organization that are referenced from Sheets 1 through 10 (sheet 1 is organization #1, etc...) When I try to use the autofill function, instead of changing the the reference page number it will increase the cell number (autofill down) or autofill to the right will increase the column name but not change the page or cell number. What I really need to be able to do is autofill down keeping the same column and cell but increasing the page reference. Any help would be appreciated. Thanks in advance. |
Edit Multiple Cells
That is most unfortunate.
I cannot replicate the problem with the samples you first posted ='5'!C36 ='5'!C377 ='5'!C38 ='5'!C39 They are formulas that return values from a test sheet 5 They will all change to 6 or 4 or whatever number I choose and return values from test sheets 6 or 4 Gord On Mon, 14 Jan 2008 17:24:02 -0800, Jenn wrote: yes the formulas options is selected I tried several variations of searching, nothing worked. "Gord Dibben" wrote: I hope you didn't use my example because there was a typo in it. '5'! not '5!' Also, in Options do you have "look in"Formulas checked? Gord Sun, 13 Jan 2008 17:27:02 -0800, Jenn wrote: the Edit Replace does not work either, says it can not find data "Gord Dibben" wrote: Jenn For the first question try EditReplace What: '5!' With: '6'! Replace all. For second question, use the INDIRECT function to fill down or across =INDIRECT("'organization #" & (ROW()) & '"!B12") entered in A1 and copied down. To copy across, change ROW() to COLUMN() Gord Dibben MS Excel MVP On Sun, 13 Jan 2008 13:59:00 -0800, Jenn wrote: I need to select multipe cells and change just the reference page for the selected cells, not the other reference information in the cell. Find and Replace does not work. Example, this are four cell, I want to just change the 5 (which is the refence page). ='5'!C36, ='5'!C377, ='5'!C38, ='5'!C39 Or, I am trying to create a spreadsheet for example January's information will have 10 rows that reference 10 different organization that are referenced from Sheets 1 through 10 (sheet 1 is organization #1, etc...) When I try to use the autofill function, instead of changing the the reference page number it will increase the cell number (autofill down) or autofill to the right will increase the column name but not change the page or cell number. What I really need to be able to do is autofill down keeping the same column and cell but increasing the page reference. Any help would be appreciated. Thanks in advance. |
Edit Multiple Cells
when I enter this function, it says there is a error, it shows the (row(1:1)
in blue. Does it make a difference what version of exel I am using. right now I am using 2002 but I have a new version it it would make a difference. "Jenn" wrote: The workbook has 12 sheets for each month they are name January, February, etc... The organization sheets are names simply 1,2,3,4,5 etc..... I would start the fill down on the January sheet, C6 Row 6 contains info from organization 1 column C Row 7 contain info from organization 2 column C etc.. Thanks again for your help "Gord Dibben" wrote: Post a sample of your actual sheet names please. I am confused. Your first post said they were "organization #1 etc." Where will you be entering the first formula to fill down? Which sheet and what cell? Gord On Sun, 13 Jan 2008 16:51:11 -0800, Jenn wrote: lets see if I put this in correct =INDIRECT("'1" & (ROW(31) & '"!C31") Organization # is Sheet 1 the Row Number in Sheet 1 is 31 and the Cell # is C31 "Gord Dibben" wrote: Jenn For the first question try EditReplace What: '5!' With: '6'! Replace all. For second question, use the INDIRECT function to fill down or across =INDIRECT("'organization #" & (ROW()) & '"!B12") entered in A1 and copied down. To copy across, change ROW() to COLUMN() Gord Dibben MS Excel MVP On Sun, 13 Jan 2008 13:59:00 -0800, Jenn wrote: I need to select multipe cells and change just the reference page for the selected cells, not the other reference information in the cell. Find and Replace does not work. Example, this are four cell, I want to just change the 5 (which is the refence page). ='5'!C36, ='5'!C377, ='5'!C38, ='5'!C39 Or, I am trying to create a spreadsheet for example January's information will have 10 rows that reference 10 different organization that are referenced from Sheets 1 through 10 (sheet 1 is organization #1, etc...) When I try to use the autofill function, instead of changing the the reference page number it will increase the cell number (autofill down) or autofill to the right will increase the column name but not change the page or cell number. What I really need to be able to do is autofill down keeping the same column and cell but increasing the page reference. Any help would be appreciated. Thanks in advance. |
Edit Multiple Cells
Jenn
My last post was this.................. In January sheet in C6 enter =INDIRECT(ROW(1:1)&"!C10") and copy down etc number of rows, which would have given you a circular reference........my error. I think you better change the C10 to C20 to test, assuming you have 10 sheets named 1 through 10 Drag/copy the formula from C6 to C15 Will return the C10 values of sheets 1,2,3,4,5 etc. No difference in Excel versions. I gotta quit doing this at night. Gord On Tue, 15 Jan 2008 18:04:01 -0800, Jenn wrote: when I enter this function, it says there is a error, it shows the (row(1:1) in blue. Does it make a difference what version of exel I am using. right now I am using 2002 but I have a new version it it would make a difference. |
Edit Multiple Cells
Correction again............
If formula is entered in C6 of January sheet there will be no circular references involved so that is not an issue. Gord On Tue, 15 Jan 2008 22:06:40 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Jenn My last post was this.................. In January sheet in C6 enter =INDIRECT(ROW(1:1)&"!C10") and copy down etc number of rows, which would have given you a circular reference........my error. I think you better change the C10 to C20 to test, assuming you have 10 sheets named 1 through 10 Drag/copy the formula from C6 to C15 Will return the C10 values of sheets 1,2,3,4,5 etc. No difference in Excel versions. I gotta quit doing this at night. Gord On Tue, 15 Jan 2008 18:04:01 -0800, Jenn wrote: when I enter this function, it says there is a error, it shows the (row(1:1) in blue. Does it make a difference what version of exel I am using. right now I am using 2002 but I have a new version it it would make a difference. |
Edit Multiple Cells
If you are still hanging in and having problems, email me personally with a copy
of the workbook unless the data is too confidential. Change the AT and DOT to un-mung my address. Gord On Tue, 15 Jan 2008 22:31:49 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Correction again............ If formula is entered in C6 of January sheet there will be no circular references involved so that is not an issue. Gord |
Edit Multiple Cells
I emailed you a copy of the workbook, I tried the function again up to C60
but it was still showing a problem with the row section. "Gord Dibben" wrote: If you are still hanging in and having problems, email me personally with a copy of the workbook unless the data is too confidential. Change the AT and DOT to un-mung my address. Gord On Tue, 15 Jan 2008 22:31:49 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Correction again............ If formula is entered in C6 of January sheet there will be no circular references involved so that is not an issue. Gord |
| All times are GMT +1. The time now is 07:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com