Moving Cell Reference after Sorting
 

hi, i'm trying to have a cell reference in an equation following a
moving data point ...

Assumption #1 - column A has data A1=1, A2=5, A3=3
Assumption #2 - column B has an equation referring to the "3" in A3

Goal - to have my equation follow the data point "3"

Interaction - I **sort** column A so that the data is ascending order,
i.e., A1=1, A2=3, A3=5

Problem - my equation refers only to cell A3 (i.e., value is now 5,
originally was 3). I want the equation to refer to wherever my data
point went (i.e., to refer to whatever cell the 3 is sorted to). How
how how?

:(((

thanks in advance for your help

steve

Moving Cell Reference after Sorting
 

You can use 3 as a constant in cojunction with the if statement and
array formula

For example,if cell A1=1,A2=5,A3=3
Instead of referring directly to cell,you can use the constant 3

Tell what you are actually trying to do get precise results
For more,visit and post your questions on
http://groups.google.co.in/group/answers-for-everything


wrote:
hi, i'm trying to have a cell reference in an equation following a
moving data point ...

Assumption #1 - column A has data A1=1, A2=5, A3=3
Assumption #2 - column B has an equation referring to the "3" in A3

Goal - to have my equation follow the data point "3"

Interaction - I **sort** column A so that the data is ascending order,
i.e., A1=1, A2=3, A3=5

Problem - my equation refers only to cell A3 (i.e., value is now 5,
originally was 3). I want the equation to refer to wherever my data
point went (i.e., to refer to whatever cell the 3 is sorted to). How
how how?

:(((

thanks in advance for your help

steve

Moving Cell Reference after Sorting
 

unfortunately using a constant there wouldn't work. if i needed to
change that data point and then re-sort it, i'd like the equation to
still refer to the data in that cell (wherever it moves after it's
sorted)

Moving Cell Reference after Sorting
 

I don't know what formulas you're using in col B, perhaps you could post a
sample ?

Usually, a VLOOKUP or an INDEX/MATCH could be used to "track" the values in
col A
so that other associated values from a reference table (assuming the values
in col A are key values which are unique within the reference table) can
continue to correspond/be returned for use in other computations,
irrespective of the sorting which may be done in col A

Example: In Sheet2's col B we could use something like this in B1:
= VLOOKUP(A1,Sheet2!A:B,2,0)
with B1 copied down

Col B will continue to return correctly the associated values from col B in
the reference table (Sheet2!A:B), irrespective of the sorting which may be
carried out on the lookup values in col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
oups.com...
unfortunately using a constant there wouldn't work. if i needed to
change that data point and then re-sort it, i'd like the equation to
still refer to the data in that cell (wherever it moves after it's
sorted)

Moving Cell Reference after Sorting
 

Typo correction, apologies. Line:
Example: In Sheet2's col B ...

should have read as:
Example: In Sheet1's col B ...

(What you're trying to do is assumed to be in Sheet1)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---