CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Morgan
Morgan

Reputation: 486

Google Sheets' dynamic data range in query function

is it possible to have a dynamic data range when using query function in Google sheet?

What I would like to do is, using a dropbox, change the data range used in query function.

For example, I have 4 tables in 4 different sheets. On my main sheet, I want using my dropbox, perform a query on my selected table. Is it necessary to do it with a script?

Upvotes: 1

Views: 5319

Answers (1)

dwmorrin
dwmorrin

Reputation: 2734

You can make a dynamic query without using a script.

The query string can contain a reference to other cells.

Example in Sheets.

This example has a pulldown for the data set in B2, a pulldown for the value set in B4. The data ranges include one from another sheet. I am using named ranges to simplify the lookup process. Each data set n is named DataN.

You can separate out the query string from the cell with the actual query function call. The trick is to build up a query string using INDIRECT, COLUMN, and VALUE. I placed this in cell A10:

="select " & mid("ABCDEFGH",COLUMN(INDIRECT(B2)),1) & " where " & mid("ABCDEFGH",VALUE(COLUMN(INDIRECT(B2)))+1,1) & "=" & """" & B4 & """"

The four quotes let us place a literal quote in the query string. The '&' character does string concatenation. The use of MID as a way of translating the COLUMN function to a letter I got from here.

Then your cell with the query uses the values of the data set pulldown (B2) and the value of the query string (A10) this like:

=QUERY(INDIRECT(B2),A10,1)

Upvotes: 1

Related Questions