Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
substitute the filename in a cell reference with a string in another cell. | Excel Discussion (Misc queries) | |||
Getting contents of a cell when cell reference is in the sheet | Excel Discussion (Misc queries) | |||
keep cell data in reference after sorting | Excel Discussion (Misc queries) | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |