Formula copy paste down in a sheet but change row letter increment

[Mike]

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!!!

 

[stew]

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

 

[stew]

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]

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]

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]

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]

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]

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]

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]

I will get back to you. Have to Run some errands

Best

Stew

 

[stew]

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]

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]

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]

Interesting, I live on a peninsula on the coast of Scituate, Massachusetts.

From Ocean person to the other. Thank you and Merry Christmas!!!