-2

This article: https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-F5ED2452-2337-4F71-BED3-C8AE6D2B276E describes the use of structured references. I hadn't heard about this, although it's been in Excel for some time.

I'm not wedded to their syntax, but I like the concept. It looks like it would be clearer and more robust for VLookup, Index/Match, and query.

In addition it gives automatic named ranges. E.g. I can do = sum(Tablename[Quarter1][Chicago]:TableName[Quarter3][Seattle])

2
  • What's your actual question?
    – ale
    Dec 30, 2019 at 15:44
  • The question is in the title. What isn't clear? May 12, 2020 at 14:24

2 Answers 2

1

Use Data->Named Ranges to manually accomplish the same thing (you will need to specify an entire column however as it will not auto-expand downward (without GAS). It is strange indeed that Google decided not to use the same table reference naming conventions but the reason is probably because they have no concept of tables. "Filter Views" are the closest thing but they are incompatible with mobile versions and do not allow you to view them simultaneously which renders them nearly useless as Excel style tables... what you could do is use the "Create a Filter" button to simulate the Excel table but it only supports one table per sheet... then you could use GAS to name those specific filter ranges in accordance with the format style you wish to use. (You will also need to enforce unique header names)

3
  • Named Ranges help, but because they are global, you quickly get into a management issue having too many of them, and Sheets doesn't have the syntax to support pulling subsets out of them, so you're back at convoluted INDEX/MATCH buried 5 () deep. Dec 23, 2019 at 15:27
  • @SherwoodBotsford you can give your "imaginary tables" names just like in the Excel naming convention... so for example PetDatabase[Age] or whatever. Then PetDatabase can be all the ranges, then as you type it will autocomplete very similar to Excel no?
    – CodeCamper
    Dec 23, 2019 at 15:57
  • it gives some of the name conventions, but the excel syntax in effect gives you automatic range names without having to enter them separately. If I applied this to my present sheet, I would ahve several hundred named ranges in the range list. Dec 31, 2019 at 2:24
1

This doesn't seem to be an area that has receive much attention for add ons.

Partial workarounds:

  1. Create a naming convention using an abbreviation of the tab as the start of the named ranges. This groups named ranges involving a given tab in the same part of the list.
  2. Name ranges are usually one column. This allows the construct VLOOKUP(searchitem {keyrange,datarange},2,FALSE)
  3. Named ranges auto expand on rows inserted, but not appended. Include a row with last elements "zzzz" as part of the range, then add new rows as needed above this row.

Your Answer

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

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