How to maintain formula/link as I reorder cells via sort?

[Ariela Daniels]

Hello

I have searched for the answer to this problem but keep getting how to create a constant which is not exactly what I need. So am posting it as a question... hopefully someone can help :)

What I am trying to do is maintain a relationship between the original data value and my formula even if I reorder my rows. Right now my formula looks at the row cell immediately above it, so if I reorder my rows to sort by a different column then it looks at the wrong number and then calculates the wrong answer. Is there a way to maintain the link?

I've included image to hopefully make more sense:

My formula in H18 references the cell above it (H17).

If I reorder my rows I want the formula to still reference the original data cell. Instead it is pointing to the cell above it (H28). In this case it is H17 still but it could have moved to say H22 and then the calculated value would be wrong.

The numbers in bold in column I show what the values of the cells should be after they have been moved around. I want the formulas to maintain the same values, even if I reorder the cells.

Using $ constants (e.g. $H$17) does not work because the reference data may move from it's original cell and then the calculated formula result becomes wrong. (e.g. if my reference data ended up in H22 then a hard reference to $H$17 would give the wrong result)

Can anyone help??

Thanks!

[#2]

@Ariela Daniels 

I have got the same issue and trying to find the solution to it. Please help.

[#3]

@Ariela Daniels 

I'd add another column for calculations like

assuming you have two records per day and description is the same. Formula depends on your version of Excel, but this one shall work on all

=[@Sum]+
 IF([@Description]="Internet/Phone",
     0,
     70*0.9+(
         INDEX([Sum],
              MATCH(1,
              INDEX(([Date]=[@Date])*([Description]="Internet/Phone"),0),0) ) -
         70)*0.8
)