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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,379
Office Version
  1. 365
Platform
  1. Windows
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.
 

jedi160

Board Regular
Joined
Jan 8, 2005
Messages
57
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!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,379
Office Version
  1. 365
Platform
  1. Windows
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.
 

jedi160

Board Regular
Joined
Jan 8, 2005
Messages
57

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,379
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,137,060
Messages
5,679,380
Members
419,824
Latest member
Mercy kiara

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
Top