1

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

1

Instead of

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

try

=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.

| improve this answer | |
  • hmm So I tried your suggestion but im getting an error on my example GoogleSheet I listed. – Donavon Jun 11 at 20:03
  • I'm sorry, it should be semicolons instead of commas (answer edited) – Rubén Jun 11 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 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 at 16:07

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.