How to incrementally move rows down on a formula

MRSM94

New Member
Joined
Jul 5, 2018
Messages
4
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Code:
=INDIRECT("'0.3a Daily Outputs CON'!$S"&column(A1)*12-4)
 
Upvote 0
Code:
=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! :)
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
='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:

Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,596
Members
452,658
Latest member
GStorm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top