modify number from cell reference in QUERY function (Google Sheets)

1
votes

Is it possible to modify a number from a cell reference within the syntax of a QUERY function in Google Sheets without a helper cell/column?

If I have "5" in a cell, can I reference that cell and somehow subtract 1 when using the LIMIT option in the QUERY syntax so it returns four results?

I am including a sample sheet below as well as working and nonworking formulas.

link to sheet & cell https://docs.google.com/spreadsheets/d/1lyCK5eIEQYjGFOxh5T_4BxeuDS-1zvMLq80IYn0dc38/edit#gid=1018697126&range=B1

working formula:

=QUERY(INDIRECT(currentTable),"limit "&B1&"",1)

nonworking attempt:

=QUERY(INDIRECT(currentTable),"limit "&B1&"-1",1)
1
google-sheetsgoogle-sheets-formulagoogle-query-language

1 Answers

1
votes

You can just subtract 1 from B1 instead before passing it into the query.

=QUERY(INDIRECT(currentTable),"limit "&B1-1,1)

Output:

output