Google-sheets – How to sort data with named-ranges without breaking references to the ranges

google sheets

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?

Best Answer

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.

Related Topic