Easy Reference Moving Question

jedi160

Board Regular
Joined
Jan 8, 2005
Messages
57
I need to move a formula one cell at a time, but the reference within that formula has to move two cells at a time. How can I do this?

Thanks,
Patrick
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Can you provide some more specifics, like the formula, where it is starting and where you are moving it to (left-right, up-down).

You can probably make use of the ROW() and COLUMN() functions to do this, by multiplying these values by 2 and adding/subtracting a constant.
 
Upvote 0
I just need to move the formula down one row, while the reference moves down 2 rows.

I'll try this for a start.

Thanks!
 
Upvote 0
For a real easy example to see how it works, enter this in Row 1:
=ROW()*2
and copy down for all rows.

This will give you the behavior you want. Now you just need to work it into your equation.
 
Upvote 0
=IF(ISERROR(DSUM(Combined!$A$1:$O$1612,12,$M$1:$P$2)/$B5)=TRUE,"",DSUM(Combined!$A$1:$O$1612,12,$M$1:$P$2)/$B5)

The sections of the code that are underlined above are what I would like to replace with the row function you gave me. How would I do that?
 
Upvote 0
If you are looking to modify range references, you will need to use the INDIRECT function, i.e.

=INDIRECT("$P$!" & ROW()*2)

Check out the Excel help on the INDIRECT function. Since your formula is already pretty long, its probably going to get a little ugly.
 
Upvote 0

Forum statistics

Threads
1,217,503
Messages
6,137,020
Members
450,038
Latest member
JackOfBlades

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