Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Absolute reference (2002/2003)

    I have an Excel spreadsheet with multiple worksheets. The data will be input in one worksheet (lets call this WS-1). In a second worksheet (lets call this WS-2) I have formulas in WS-2 linked to cells in WS-1. My question is this:

    Is there a function I can use in the cell formulas of WS-2 so that when the linked cells (the data entry cells) in WS-1 are sorted the cells in WS-2 will still be linked to the original data?

    For example: the data in cell A1 of WS-1 is the text "TEST"; after being sorted the data "TEST" is now in cell A5; I want a given cell in WS-2 to follow the new position of this data so that it reads "TEST" before (by reading cell A1) and after (by reading cell A5) the sorting of the data in WS-1.

    Please let me know if this is feasible.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Absolute reference (2002/2003)

    Excel doesn't adjust cell references in formulas when you sort a range. I think you'll have to resort to a VLOOKUP or INDEX + MATCH formula to refer to a fixed value instead of a fixed cell in a range that might be sorted.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Absolute reference (2002/2003)

    Thanks, Hans.

    I use Vlookup, but how do you use Index + Match to keep the reference to the same information?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Absolute reference (2002/2003)

    That depends on the situation. VLOOKUP is in fact a specialized combination of MATCH and INDEX.
    VLOOKUP looks up the search value in the first column of a table and returns the corresponding value from another column in the same table.
    With MATCH you can look up the search value in a column and then use INDEX to return the corresponding value in another column that may be completely unrelated to the search column.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Absolute reference (2002/2003)

    Thanks again, Hans.

    I will see if we can get that to work for us.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •