Cell Reference in a Formula

[Guest]

I have the following formula in cells A33 and A34:

=+A3&" Current Year" A33
=+A3&" Carry Over" A34

Is there a way to copy/paste both of the formulas at the same time to cells
A35 and A36 and have the forumla reference update to A4 in both formulas?
When I copy/paste the formulas to A35 and A36, the reference in the formulas
go to cell A5:

=+A5&" Current Year"
=+A5&" Carry Over"

Thanks
Dave

 

[Guest]

It would probably be best if you rearranged your formulas so that the carry
over formula would be beside the current year formula, in column B, instead
of below it. That way, you could fill down all you like, and the formulas
would update incrementally.

 

[Ron Rosenfeld]

I have the following formula in cells A33 and A34:

=+A3&" Current Year" A33
=+A3&" Carry Over" A34

Is there a way to copy/paste both of the formulas at the same time to cells
A35 and A36 and have the forumla reference update to A4 in both formulas?
When I copy/paste the formulas to A35 and A36, the reference in the formulas
go to cell A5:

=+A5&" Current Year"
=+A5&" Carry Over"

Thanks
Dave

One way is to devise a formula with OFFSET so that the OFFSET is properly
computed. Here's one that is specific to your requirements. It would have to
be adjusted if the differences were other than "30" and "31". But probably
only the "+13" would have to be changed.

A33: =OFFSET(A33,-(INT(MOD(ROW()/2,30))+MOD(ROW(),2)+13),0) & " Current Year"
A34: =OFFSET(A33,-(INT(MOD(ROW()/2,30))+MOD(ROW(),2)+13),0)& "Carryover"


--ron