I have a spreadsheet with many sheets. (Data1 Data2 and Data3). One sheet is named Search. This search sheet will search all 3 sheets (Data1,Data2,Data3)

Results will return on the sheet named Search. Columns A, B and C will be returned under the search box. The results need to be 'clickable'. When clicked, it takes you to the sheet and row you were searching for.

I am just trying to search across many sheets. The standard search feature in Google Sheets is not what I want.

My code:

=QUERY(Data1!A2:C, Data2!A2:C, Data3!A2:C "SELECT * WHERE A "&E3&" '"&F3&"'")

How do I make the results 'hyperlinks' to the sheet and row the user is looking for?

Example Page with editor permissions: https://docs.google.com/spreadsheets/d/1jus_FSSy4duqDpxd4Si1jov4PXfj3ilqV_gwxKuwjcE/edit#gid=0


Instead of

=QUERY(Data1!A2:C, Data2!A2:C, Data3!A2:C "SELECT * WHERE A "&E3&" '"&F3&"'")


=QUERY({Data1!A2:C; Data2!A2:C; Data3!A2:C}, "SELECT * WHERE Col1 "&E3&" '"&F3&"'")

The above because QUERY doesn't allow multiple ranges but it could use an array.

  • hmm So I tried your suggestion but im getting an error on my example GoogleSheet I listed.
    – Donavon
    Jun 11 '20 at 20:03
  • I'm sorry, it should be semicolons instead of commas (answer edited)
    – Rubén
    Jun 11 '20 at 20:13
  • Darn, I updated with your suggestions and still no luck. I disabled my data validation drop down and still no dice.
    – Donavon
    Jun 12 '20 at 1:22
  • 1
    This did work! I discovered that the search I was performing REQUIRED my typed text to be in all caps, once I did this the search worked. I need to figure out how to wrap the query to accept lower case text.
    – Donavon
    Jun 12 '20 at 16:07
  • Please read What should I do when someone answers my question?
    – Rubén
    Jun 12 '20 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.