Easy Reference Moving Question

jedi160

Board Regular
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.

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!

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.

=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?

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.

That worked!!! Thanks a lot!

Replies
1
Views
180
Replies
6
Views
161
Replies
9
Views
110
Replies
7
Views
439
Replies
3
Views
149

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

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.

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

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