asked on

Keep Same Cell Reference when moving row

In the attached file I'd like to keep the cell references the same despite the fact that I have to update the data in cells C2:H2.

To update I click inset a row which means the data moves down. When this happens the cell references increment one every time I add a row. That means instead of remaining "C2:G47" the cell references increment by one as shown in the formula below. The formulas are in cells Q4:Y11.

=IFERROR(AGGREGATE(15,6,$C$3:$G$48/($C$3:$G$48>P4),1),"")

If it cannot be done with the formulas and data in same sheet. I will need the code to use in the formula to reference from the new page to the data page.
KeepSameCellReferenceWhenMovingCell.xlsm

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

Another way to bypass your problem is to

select row 3

Ctrl-c

right-click row 4

select "Insert copied cells"

Then reenter data in row 3

Pedro

ASKER

Saqib,

Your solution did not work. While C2 remains the same, G47 Changes to G48. Therefore, this solution will not work.

Byunt,

I need clarification. My Original formula reads:

=IFERROR(AGGREGATE(15,6,$C$2:$G$47/($C$2:$G$47>P3),1),"")

but when I add one row it changes to:

=IFERROR(AGGREGATE(15,6,$C$3:$G$48/($C$3:$G$48>P4),1),"")

Your formulas are:

=IFERROR(AGGREGATE(15,6,OFFSET($C$1:$G$1,1,0,46)/(OFFSET($C$1:$G$1,1,0,46)>P4),1),"")

=IFERROR(AGGREGATE(15,6,INDEX($C:$C,2):INDEX($G:$G,47)/(INDEX($C:$C,2):INDEX($G:$G,47)>P4),1),"")

What I need to know is does each formula use the C2:G47 range?

If so, What do I change when I need to refer to a different range of cells?

byundt

OFFSET($C$1:$G$1,1,0,46) returns a 46 row range starting 1 row below C1:G1. Since the header labels always remain in C1:G1, when you insert new rows below that OFFSET will always return C2:G47.