5

How do I create a named range which refers to cell content, not cell location? Or, how do I sort but preserve a 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 content.

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?

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.

  • 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 '15 at 15:54
  • 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 at 23:54
-1

Excel handles this kind of sorted or filtered range reference just fine. Its a pretty serious shortcoming of google sheets, apparently.

  • Yup. I like Google products, especially what I can do with Google Apps Script but the more I use it the more shortcomings I see compared to Excel. They make great products but none are really ready for large scale enterprise use where productivity and capabilities are crucial. – IMTheNachoMan Oct 2 at 2:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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