Easy Reference Moving Question

[jedi160]

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

 

[Joe4]

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]

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]

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]

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

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.

 

[jedi160]

That worked!!! Thanks a lot!

 

[Aladin Akyurek]

You might want to consult:

http://support.microsoft.com/default.aspx?scid=kb;en-us;282851