Sort affects formula

[Its-Just-Me]

I use Excel to manage records (rows) of data.
Data (a cell) in any given row uses a formula which makes use of data from a
different row. The rows may or may not be sequential.
When I sort the rows the formulas, since the rows change order, they no
longer point to the correct data (cell).
It there a straight forward way of accomplishing this?
Thanks.

 

[John C]

When you sort the data, are you sorting the formulas with the data? Give us
an example of data, and an example of the formulas that were right, and now
wrong.

 

[ShaneDevenshire]

Without more details, the best I can suggest is make the references absolute
- change =A1 to =A$1

What we really need to know is what's the formula, does it refer to cells in
the data or outside the data.

 

[Its-Just-Me]

The table stores exercise data, ie "Barbell Curls (BBCurls)" might be one row
(cell A1), "Barbell Extensions (BBExten)" might be row 2 (cell A2) etcetera.
Each exercise would belong to a set ie "A" (cell B1), "B" (cell B2), (five
exercises to a set) so when sorted on column B the exercises would be sorted
by what set they belong to. When sorted by Column A the exercises would be in
Alphabetical order by name.
Column 'C' might be the maximum weight used in each exercise.
Exercise "Barbell Curls" and "Barbell Extensions" are related because they
are opposites so I would want to be warned when ("Barbell Curls" Maximum
Weight)x0.50 > ("Barbell Extensions" Maximum Weight)

BBCurls, A, 100, (=C2*0.5)
BBExten, B, 30, (=C1*0.5)
BBPress, A, 150, (=C4*0.5)
BBPull, B, 100, (=C3*0.5)

I set Conditional Formating on column D to : IF "Cell Value is" "Less than"
"=$D1" THEN Format Red.
It works fine until I sort by 'set' (Column B)

Thanks again.
(Sorry, I'm new to this)

 

[John C]

One possible solution, is used defined names. Takes a 'little' bit of setup,
but once setup, should be easy to configure. Given your sample data, this is
what I did:
My named ranges are:
Column A: Name
Column B: Group
Column C: Weight
Row 1: BBCurls
Row 2: BBExten
Row 3: BBPress
Row 4: BBPull

The easiest way to 'name' the ranges is, for example, click on the A for the
first column (this should select the entire column), and in the formula bar,
to the right of the formula, click in there, and type in the word Name. Do
the same for columns B & C, and click on the row number to highlight the
entire row and name as above. (Note: This assumes your inital presorted
setup).
After you do the naming, your formula in cell D1 would look like this:
D1: =BBExten Weight*0.5
D2: =BBCurls Weight*0.5
D3: =BBPull Weight*0.5
D4: =BBPress Weight*0.5

Then, when the data is sorted, the named ranges get sorted as well, and
therefore your data remains exact.

Hope this helps.

 

[Its-Just-Me]

Sorry! No it didn't work.

Actual Sample copied from worksheet: (Changed BBPull to 200 for clarity)
Sorted on Column B.
Column G is the expected answers. Column E are the results I got.
A B C D E F G
BBCurls A 100 75 C2*0.5 wrong C3*0.5
BBPress A 150 100 C4*0.5 right C4*0.5
BBExten B 30 50 C1*0.5 right C1*0.5
BBPull B 200 15 C3*0.5 wrong C4*0.5

BBExten is the opposite of BBCurls so D1 should be 15 (30*0.5) (not 75).
D4 should be 75 (150*.05) (not 15)

Any ideas??
Thanks

 

[Its-Just-Me]

Hi Shane
I posted more details.
Thanks for your consideration

 

[John C]

you are right, my error, i didn't error check.
With your original data setup, type this into column D for BBCurls
=INDEX($C$1:$C$4,INDEX(MATCH("BBExten",$A$1:$A$4,0),1))*0.5
Using that formula, modify each cell in column D to match it's "opposite"
so, BBExten would be:
=INDEX($C$1:$C$4,INDEX(MATCH("BBCurls",$A$1:$A$4,0),1))*0.5

 

[Its-Just-Me]

Worked GREAT!
Thanks John.

you are right, my error, i didn't error check.
With your original data setup, type this into column D for BBCurls
=INDEX($C$1:$C$4,INDEX(MATCH("BBExten",$A$1:$A$4,0),1))*0.5
Using that formula, modify each cell in column D to match it's "opposite"
so, BBExten would be:
=INDEX($C$1:$C$4,INDEX(MATCH("BBCurls",$A$1:$A$4,0),1))*0.5

 

[John C]

Thanks for the feedback. If you get back this way, be sure to check "YES"
that the question has been answered