How do I create a named range which refers to cell value, not cell location? Or, how do I sort but preserve the value of named range?
I have a Google Sheet which has several named cells. When I sort the sheet, other cells which refer to the named range(s) change value because the name refers to the cell by location, not value.
A named range,
bananaCount refers to
A5 is a formula which uses the named range:
If I sort the sheet by column
A (fruit names) the value of
B2 and the the
bananaCount named range will become
1 because row 3 (
Apple, ...) becomes row 2, which causes
A5 changes to
1. I want
A5 to remain
B3 after sorting).
Now, if I cut/paste
B2, the named range reference does update correctly. However, sorting does not update the reference.
(What I really want is a variable to refer to the cell value, regardless of moving the cell value elsewhere in the sheet by any mechanism -- either sort or copy/cut/paste.)