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

Improve this question

1 Answer 1

Reset to default
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
5
  • hmm So I tried your suggestion but im getting an error on my example GoogleSheet I listed.
    – Donavon
    Commented Jun 11, 2020 at 20:03
  • I'm sorry, it should be semicolons instead of commas (answer edited)
    – Rubén - Volunteer Moderator - ♦
    Commented Jun 11, 2020 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
    Commented Jun 12, 2020 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
    Commented Jun 12, 2020 at 16:07
  • Please read What should I do when someone answers my question?
    – Rubén - Volunteer Moderator - ♦
    Commented Jun 12, 2020 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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