I am trying to create a health related spreadsheet that has a lot of data - a lot of which isn't relevant to this question so I've simplified it. There is a column for each type of pain where you write on a scale of 0-10 how intense your pain was, and another column for any relevant notes. The data is broken up into named ranges to make it easier to display on different tabs (HeadData = Head Pain, ChestData = Chest Pain, etc. - 15 named ranges in total.)
One of the tabs I'm working on has a table where you are viewing only the specific named range, in this case HeadData.
=query({HeadData}, " Select * where Col1 is not null ",1)
This works perfectly, but I want to replace {HeadData} with a reference cell to a drop menu so you can select the specific pain area column you want to be displayed.
If I put the reference cell in G1 with a drop down list of the named ranges and select ChestData and try to do
=query({&G1&}, " Select * where Col1 is not null ",1)
It is only picking up G1 (ChestData) as a string and not the actual named range.
So my question is, is there a way to make a drop menu containing named ranges that turn into actual sets of data and not strings when placed in the data section of the query?
Here is my spreadsheet, any help is appreciated. Thanks! https://docs.google.com/spreadsheets/d/1CcuSV2bbfxsUPPkmj-fru2yYYmmtpXk9LKEF85sxVUw/edit?usp=sharing
You can use INDIRECT for this.
INDIRECT Returns a cell reference specified by a string.
Change your formula to
=query({INDIRECT(G1)}, " Select * where Col1 is not null ",1)
The INDIRECT function will convert the string in G1 to a cell reference and then the rest of your formula will query the relevant named range.