6

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:

sort-with-named-range

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?

Improve this question

1 Answer 1

Reset to default
3

You can't really have "variables" in a spreadsheet; it's not a programming language. The values are contained in cells and can be referred to by location, possibly relative to location of other cells. If cells get rearranged so that the relations within rows or columns are lost, that is already a problem.

In your example, you want to refer to Count of Bananas in a way that is independent of sorting. The function vlookup does this nicely: 

=vlookup("Banana", A:B, 2, False)

This means: get the value in column B that's in the row with "Banana".

Of course, if you sort column B but not column A, this is going to break; but then the entire logic of Fruit/Count columns is broken.

Improve this answer
2
  • This will work, I can keep the column order stable to preserve a reference to the second column (B2); and you're right, I'll never sort by one column and not another. Thanks for the suggestion to use vlookup.
    – Richard Michael
    Nov 9, 2015 at 15:54
  • 1
    but here's the thing... if you insert or delete rows, the named range is automatically adjusted, whereas with a sort it is not. that seems like of like a bug or oversight or at the very least an inconsistency which is very annoying in my opinion.
    – Michael
    Feb 4, 2019 at 23:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.