Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort affects formula
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort affects formula
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. -- ** John C ** "Its-Just-Me" wrote: 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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort affects formula
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. -- Thanks, Shane Devenshire "Its-Just-Me" wrote: 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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort affects formula
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) "ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "Its-Just-Me" wrote: 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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort affects formula
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 a 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. -- ** John C ** "Its-Just-Me" wrote: 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) "ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "Its-Just-Me" wrote: 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. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort affects formula
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 "John C" wrote: 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 a 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. -- ** John C ** "Its-Just-Me" wrote: 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) "ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "Its-Just-Me" wrote: 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. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort affects formula
Hi Shane
I posted more details. Thanks for your consideration "ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "Its-Just-Me" wrote: 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. |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort affects formula
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 ** "Its-Just-Me" wrote: 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 "John C" wrote: 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 a 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. -- ** John C ** "Its-Just-Me" wrote: 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) "ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "Its-Just-Me" wrote: 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. |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort affects formula
Worked GREAT!
Thanks John. "John C" wrote: 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 ** "Its-Just-Me" wrote: 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 "John C" wrote: 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 a 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. -- ** John C ** "Its-Just-Me" wrote: 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) "ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "Its-Just-Me" wrote: 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. |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sort affects formula
Thanks for the feedback. If you get back this way, be sure to check "YES"
that the question has been answered :) -- ** John C ** "Its-Just-Me" wrote: Worked GREAT! Thanks John. "John C" wrote: 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 ** "Its-Just-Me" wrote: 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 "John C" wrote: 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 a 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. -- ** John C ** "Its-Just-Me" wrote: 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) "ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "Its-Just-Me" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sales history and how Unit Price affects it... | Charts and Charting in Excel | |||
hidden macro affects how other files open | Excel Discussion (Misc queries) | |||
One workbook affects another | Excel Discussion (Misc queries) | |||
Value part of formula exlusion/ Not affects neg's: value | Excel Discussion (Misc queries) | |||
The cursor affects all cells it touches-how to correct? | New Users to Excel |