How to incrementally move rows down on a formula

[MRSM94]

Hey all,

Thanks for the help in advance.

I am using this formula currently to pull a cell value from another sheet:

='0.3a Daily Outputs CON'!$S8

When I drag the formula to the right, I want to to change the cell reference to S20 - I.E Move 12 rows down in the referenced sheet (as this is the data I want to pick up. I want to continue to do this so that I can pull all of the data from the sheet as there is a lot (and also different tabs where I want to replicate the formula. At the minute when I drag it across the formula stays the same

Is there any easy way to do this? So that when I drag the formula horizontally it goes S20, then S32 etc. etc.

Thanks!

S
x

 

[shaowu459]

=INDIRECT("'0.3a Daily Outputs CON'!$S"&column(A1)*12-4)

 

[MRSM94]

=INDIRECT("'0.3a Daily Outputs CON'!$S"&column(A1)*12-4)

Thanks for the quick reply! Where do I paste this code? into the cell where I want to start pulling the data? What does the A1 mean? When I paste this into my excel its pulling the value 0? I want it to start from S8 in the referenced sheet and then go to S20 next etc, Thanks!

 

[shaowu459]

paste in any cell you want and copy formula to the right. Please check S8, if the formula returns 0, S8 may be a blank cell or stored 0 in it.

 

[MRSM94]

paste in any cell you want and copy formula to the right. Please check S8, if the formula returns 0, S8 may be a blank cell or stored 0 in it.

Amazing. Thank you. Where in the formula is it referencing S8? As I want to put it in the cell below now and reference S9 etc.

Thanks!

 

[MRSM94]

I have worked it out thank you!

I seem to have a new issue now -

I am trying to work out the weekly average from some data:

=('0.3 Data Source - DO NOT EDIT'!E5+'0.3 Data Source - DO NOT EDIT'!F5+'0.3 Data Source - DO NOT EDIT'!G5)/('0.3 Data Source - DO NOT EDIT'!E88+'0.3 Data Source - DO NOT EDIT'!F88+'0.3 Data Source - DO NOT EDIT'!G88)

However, I want this to roll over each column (I.e Week 1, Week 2)

How do I make it so that when I drag the formula across to Week too, it pulls data from E10+F10+G10 etc etc so that I can easily calculate the weekly averages as I go on?

Thanks so much for the help!

S
X

 

[shaowu459]

='0.3 Data Source - DO NOT EDIT'!E5+'0.3 Data Source - DO NOT EDIT'!F5+'0.3 Data Source - DO NOT EDIT'!G5 equals to:

=SUM(INDIRECT("'0.3 Data Source - DO NOT EDIT'!E"&ROW(A1)*5&":F"&ROW(A1)*5))

copy formula down, you will get result equals to E10+F10+G10. If you want copy formula to the right cell, replace row(a1) with column(a1).

Hope this helps.