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?
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 ?
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])
See also:
User | Count |
---|---|
17 | |
12 | |
5 | |
5 | |
5 |