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.
For example:
A named range, bananaCount
refers to B2
.
A5
is a formula which uses the named range: =bananaCount
.
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 5
(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.)
Any suggestions?