|
Formula copy paste down in a sheet but change row letter increment
I am trying to copy and paste a Range B6-O11 in the same sheet numerous
times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter increment
Hi Mike Not sure I understand, but to Limit to a few Questions What Formula is in B6 and you want to paste that to what cell? on the same sheet? Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter increment
Hi Mike,
I think this is what you are after Copy this to B6 and Drag it Down B and paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+5,(COLUMN())+ROW()-5)) In C6 Paste this and Drag Down C and Paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+4,(COLUMN())+ROW()-5)) You will notice that the only difference is the +5 has become +4 This Minus 1 has to be done every time you move 1 column along. I am sure that there will be a Smarter way But this works Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter incre
Good Morning Stew,
Appreciate your reply! The formula in B6 is ='Open Data File'!C$7 The Cell I want to paste to is B14, B22, B30 and so on... But, although it is being pasted I want the formula to read ='Open Data File'D$7 and in B22 to read ='Open Data File'E$7 and in C30 to read ='Open Data File'F$7 and so on... Thanks, Mike "stew" wrote: Hi Mike Not sure I understand, but to Limit to a few Questions What Formula is in B6 and you want to paste that to what cell? on the same sheet? Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter incre
Small Error
This is correct Copy this to B6 and Drag it Down B and paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+5,(COLUMN())+ROW()-5)) In C6 Paste this and Drag Down C and Paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+5,(COLUMN())+ROW()-6)) You will notice that the only difference is the -5 has become -6 This Minus 1 has to be done every time you move 1 column along. I am sure that there will be a Smarter way But this works Best Stew "stew" wrote: Hi Mike, I think this is what you are after Copy this to B6 and Drag it Down B and paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+5,(COLUMN())+ROW()-5)) In C6 Paste this and Drag Down C and Paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+4,(COLUMN())+ROW()-5)) You will notice that the only difference is the +5 has become +4 This Minus 1 has to be done every time you move 1 column along. I am sure that there will be a Smarter way But this works Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter incre
Sorry Mike
I missunderstood. What I have Given you will Change C to D to E etc on every Row Change. What You want is on every 8th row change C to D to E etc Is That Correct? Its Afternoon Here Best stew "Mike" wrote: Good Morning Stew, Appreciate your reply! The formula in B6 is ='Open Data File'!C$7 The Cell I want to paste to is B14, B22, B30 and so on... But, although it is being pasted I want the formula to read ='Open Data File'D$7 and in B22 to read ='Open Data File'E$7 and in C30 to read ='Open Data File'F$7 and so on... Thanks, Mike "stew" wrote: Hi Mike Not sure I understand, but to Limit to a few Questions What Formula is in B6 and you want to paste that to what cell? on the same sheet? Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter incre
Hi Stew'
I copied the formula given into B6 and when I copy and paste it to B14 it returns the data in column K cell 6 from the sheet Open Data File...It needs to return the data in column D cell 6... Thanks, Mike "stew" wrote: Hi Mike, I think this is what you are after Copy this to B6 and Drag it Down B and paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+5,(COLUMN())+ROW()-5)) In C6 Paste this and Drag Down C and Paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+4,(COLUMN())+ROW()-5)) You will notice that the only difference is the +5 has become +4 This Minus 1 has to be done every time you move 1 column along. I am sure that there will be a Smarter way But this works Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter incre
Sorry Mike
I missunderstood. What I have Given you will Change C to D to E etc on every Row Change. What You want is on every 8th row change C to D to E etc Is That Correct? Its Afternoon Here Best stew "Mike" wrote: Hi Stew' I copied the formula given into B6 and when I copy and paste it to B14 it returns the data in column K cell 6 from the sheet Open Data File...It needs to return the data in column D cell 6... Thanks, Mike "stew" wrote: Hi Mike, I think this is what you are after Copy this to B6 and Drag it Down B and paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+5,(COLUMN())+ROW()-5)) In C6 Paste this and Drag Down C and Paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+4,(COLUMN())+ROW()-5)) You will notice that the only difference is the +5 has become +4 This Minus 1 has to be done every time you move 1 column along. I am sure that there will be a Smarter way But this works Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter incre
Sorry, Good Afternoon!
Yes that is what am am trying to do...In every 8th row change to C to D to E etc... sorry if my description confused you... Thanks, Mike "stew" wrote: Sorry Mike I missunderstood. What I have Given you will Change C to D to E etc on every Row Change. What You want is on every 8th row change C to D to E etc Is That Correct? Its Afternoon Here Best stew "Mike" wrote: Hi Stew' I copied the formula given into B6 and when I copy and paste it to B14 it returns the data in column K cell 6 from the sheet Open Data File...It needs to return the data in column D cell 6... Thanks, Mike "stew" wrote: Hi Mike, I think this is what you are after Copy this to B6 and Drag it Down B and paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+5,(COLUMN())+ROW()-5)) In C6 Paste this and Drag Down C and Paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+4,(COLUMN())+ROW()-5)) You will notice that the only difference is the +5 has become +4 This Minus 1 has to be done every time you move 1 column along. I am sure that there will be a Smarter way But this works Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter incre
I will get back to you. Have to Run some errands
Best Stew "Mike" wrote: Sorry, Good Afternoon! Yes that is what am am trying to do...In every 8th row change to C to D to E etc... sorry if my description confused you... Thanks, Mike "stew" wrote: Sorry Mike I missunderstood. What I have Given you will Change C to D to E etc on every Row Change. What You want is on every 8th row change C to D to E etc Is That Correct? Its Afternoon Here Best stew "Mike" wrote: Hi Stew' I copied the formula given into B6 and when I copy and paste it to B14 it returns the data in column K cell 6 from the sheet Open Data File...It needs to return the data in column D cell 6... Thanks, Mike "stew" wrote: Hi Mike, I think this is what you are after Copy this to B6 and Drag it Down B and paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+5,(COLUMN())+ROW()-5)) In C6 Paste this and Drag Down C and Paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+4,(COLUMN())+ROW()-5)) You will notice that the only difference is the +5 has become +4 This Minus 1 has to be done every time you move 1 column along. I am sure that there will be a Smarter way But this works Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter incre
Hi Mike
OK Try this in B6 and Paste down =INDIRECT("'open data file'!"&ADDRESS(7,(INT(ROW()+2)/8)+2)) Try this in C6 and Note the difference and Paste Down =INDIRECT("'open data file'!"&ADDRESS(8,(INT(ROW()+2)/8)+2)) Let Me Know Best Stew "Mike" wrote: Sorry, Good Afternoon! Yes that is what am am trying to do...In every 8th row change to C to D to E etc... sorry if my description confused you... Thanks, Mike "stew" wrote: Sorry Mike I missunderstood. What I have Given you will Change C to D to E etc on every Row Change. What You want is on every 8th row change C to D to E etc Is That Correct? Its Afternoon Here Best stew "Mike" wrote: Hi Stew' I copied the formula given into B6 and when I copy and paste it to B14 it returns the data in column K cell 6 from the sheet Open Data File...It needs to return the data in column D cell 6... Thanks, Mike "stew" wrote: Hi Mike, I think this is what you are after Copy this to B6 and Drag it Down B and paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+5,(COLUMN())+ROW()-5)) In C6 Paste this and Drag Down C and Paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+4,(COLUMN())+ROW()-5)) You will notice that the only difference is the +5 has become +4 This Minus 1 has to be done every time you move 1 column along. I am sure that there will be a Smarter way But this works Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter incre
Hi Stew,
Yes!!! It works perfect! This is the first time I used this community network. Thanks for all of your time & help. It is greatly appreciated. Enjoy the Holidays... Thanks, Mike "stew" wrote: Hi Mike OK Try this in B6 and Paste down =INDIRECT("'open data file'!"&ADDRESS(7,(INT(ROW()+2)/8)+2)) Try this in C6 and Note the difference and Paste Down =INDIRECT("'open data file'!"&ADDRESS(8,(INT(ROW()+2)/8)+2)) Let Me Know Best Stew "Mike" wrote: Sorry, Good Afternoon! Yes that is what am am trying to do...In every 8th row change to C to D to E etc... sorry if my description confused you... Thanks, Mike "stew" wrote: Sorry Mike I missunderstood. What I have Given you will Change C to D to E etc on every Row Change. What You want is on every 8th row change C to D to E etc Is That Correct? Its Afternoon Here Best stew "Mike" wrote: Hi Stew' I copied the formula given into B6 and when I copy and paste it to B14 it returns the data in column K cell 6 from the sheet Open Data File...It needs to return the data in column D cell 6... Thanks, Mike "stew" wrote: Hi Mike, I think this is what you are after Copy this to B6 and Drag it Down B and paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+5,(COLUMN())+ROW()-5)) In C6 Paste this and Drag Down C and Paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+4,(COLUMN())+ROW()-5)) You will notice that the only difference is the +5 has become +4 This Minus 1 has to be done every time you move 1 column along. I am sure that there will be a Smarter way But this works Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter incre
I have been helped so many times by this group of people it is good to help someone else. Merry Christmas from a small Island of the coast of Scotland sTEWART "Mike" wrote: Hi Stew, Yes!!! It works perfect! This is the first time I used this community network. Thanks for all of your time & help. It is greatly appreciated. Enjoy the Holidays... Thanks, Mike "stew" wrote: Hi Mike OK Try this in B6 and Paste down =INDIRECT("'open data file'!"&ADDRESS(7,(INT(ROW()+2)/8)+2)) Try this in C6 and Note the difference and Paste Down =INDIRECT("'open data file'!"&ADDRESS(8,(INT(ROW()+2)/8)+2)) Let Me Know Best Stew "Mike" wrote: Sorry, Good Afternoon! Yes that is what am am trying to do...In every 8th row change to C to D to E etc... sorry if my description confused you... Thanks, Mike "stew" wrote: Sorry Mike I missunderstood. What I have Given you will Change C to D to E etc on every Row Change. What You want is on every 8th row change C to D to E etc Is That Correct? Its Afternoon Here Best stew "Mike" wrote: Hi Stew' I copied the formula given into B6 and when I copy and paste it to B14 it returns the data in column K cell 6 from the sheet Open Data File...It needs to return the data in column D cell 6... Thanks, Mike "stew" wrote: Hi Mike, I think this is what you are after Copy this to B6 and Drag it Down B and paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+5,(COLUMN())+ROW()-5)) In C6 Paste this and Drag Down C and Paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+4,(COLUMN())+ROW()-5)) You will notice that the only difference is the +5 has become +4 This Minus 1 has to be done every time you move 1 column along. I am sure that there will be a Smarter way But this works Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
Formula copy paste down in a sheet but change row letter incre
Interesting, I live on a peninsula on the coast of Scituate, Massachusetts.
From Ocean person to the other. Thank you and Merry Christmas!!! "stew" wrote: I have been helped so many times by this group of people it is good to help someone else. Merry Christmas from a small Island of the coast of Scotland sTEWART "Mike" wrote: Hi Stew, Yes!!! It works perfect! This is the first time I used this community network. Thanks for all of your time & help. It is greatly appreciated. Enjoy the Holidays... Thanks, Mike "stew" wrote: Hi Mike OK Try this in B6 and Paste down =INDIRECT("'open data file'!"&ADDRESS(7,(INT(ROW()+2)/8)+2)) Try this in C6 and Note the difference and Paste Down =INDIRECT("'open data file'!"&ADDRESS(8,(INT(ROW()+2)/8)+2)) Let Me Know Best Stew "Mike" wrote: Sorry, Good Afternoon! Yes that is what am am trying to do...In every 8th row change to C to D to E etc... sorry if my description confused you... Thanks, Mike "stew" wrote: Sorry Mike I missunderstood. What I have Given you will Change C to D to E etc on every Row Change. What You want is on every 8th row change C to D to E etc Is That Correct? Its Afternoon Here Best stew "Mike" wrote: Hi Stew' I copied the formula given into B6 and when I copy and paste it to B14 it returns the data in column K cell 6 from the sheet Open Data File...It needs to return the data in column D cell 6... Thanks, Mike "stew" wrote: Hi Mike, I think this is what you are after Copy this to B6 and Drag it Down B and paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+5,(COLUMN())+ROW()-5)) In C6 Paste this and Drag Down C and Paste =INDIRECT("'open data file'!"&ADDRESS(COLUMN()+4,(COLUMN())+ROW()-5)) You will notice that the only difference is the +5 has become +4 This Minus 1 has to be done every time you move 1 column along. I am sure that there will be a Smarter way But this works Best Stew "Mike" wrote: I am trying to copy and paste a Range B6-O11 in the same sheet numerous times. The formulas in the range =Open Data File!C$7 thru =Open Data File!C$46 When I copy and paste the range down the same sheet I need the C to change to D then on the next paste change to E and so on with each time I paste the Range. In the sheet Open Data File, new data will be entered in each column C, D, E, F, G and so on in rows 6 thru 46. I am trying summarizing that data in each column on another sheet. But each time I copy then paste the range the column in all formulas in range noted above stay a C... Your help is greatly appreciated!!! |
| All times are GMT +1. The time now is 12:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com