Tables, sheets, lists, and named ranges

FHLPC · 04-15-2021 06:02 PM

If my app uses multiple tables, e.g. 20 tables, I will need to use 20 separate sheet. That’s my current setup.

20 sheet seems to be wasted when some tables are only 1 or 2 columns holding a list (without the need to delete rows).

I know that relational databases work the same way, but I was hoping that a spreadsheet would use named ranges or columns, as separate tables (only when possible, when it’s a simple list).

If the app needs a dropdown list, can the list be pulled from a named range? Creating one dedicated sheet for 1 column and few rows, seems a waste.

Is it possible to use multiple tables within one sheet/tab? Can named ranges be used as a table, or as a list?

Aurelien

Hi @FHLPC

You cannot split one single Sheets into various tables.
I have no knowledge of a way to use Named ranges neither.

However, you can use slices, in Data Section of the editor.
Within each Slice, you can add or remove any columns from your table, and you can filter out rows depending on some conditions you define.
You can then uses these Slices, which are “fraction of tables”, as any other table.

If you need to produce a specific dropdown list, many posts have been solved about this question, I suggest you type “dropdown list” in the community search bar, you will find many answers. Please have a look to this article as well, that may help you:

Does these elements answer your needs ?

Steve

Nope.

Nope, though as @Aurelien suggests, you can build multiple slices atop a single worksheet. A slice can be used in place of a table in most ways. The approach I use:

Create a table to contain values of a particular type, like Text, with three columns: the key column, a context column, and a value column. The key column is just the unique ID for each row. The context column identifies the context for which the value is intended, like Department names or Product colors. The value column is whatever value I’d want included in that context.

Key Context Value

1 Department names Sales

2 Department names Customer Service

3 Department names Operations

4 Product colors Red

5 Product colors Yellow

6 Product colors Green
Create slices atop that table corresponding to each context:

Slice Row filter expression

Department names ("Department names" = [Context])

Product colors ("Product colors" = [Context])
Reference the slices in you Valid If and Suggested values expressions:
```
 SORT(Department names[Value])
```

Key	Context	Value
1	Department names	Sales
2	Department names	Customer Service
3	Department names	Operations
4	Product colors	Red
5	Product colors	Yellow
6	Product colors	Green

Slice	Row filter expression
Department names	`("Department names" = [Context])`
Product colors	`("Product colors" = [Context])`