-1

I have a bunch of sheets I use for personal work. They have a bunch of different car parts under different tabs in each sheet.

I created a master sheet that importrange's from all of them and shows links to them in a master tab to jump to each tab separately. (doors, hoods, lightbulbs, door trims, roof racks, box of crown vic parts; its all over the place)

Is there a way for a user to search some text in a cell and have the column next to it populate it with results with matching words and ultimately, link to the tab and row that the item exists on?

ex: I have a sheet called "Search" and I type in A2 "crown vic". Then it will populate B2:B100 with any items found in the entire sheet with the words "crown vic" in it, and C2:C100 will have a link to the tabbed sheet that it is in.

Link to a test page to get my idea across:

https://docs.google.com/spreadsheets/d/1WrImPYHhhMOOZbf-AE2sNs82xL-u8wWOW4IFel6RGcY/edit#gid=999756632

I believe it would be better for me to use Javascript and HTML to create a web database for all this info instead of using sheets since its limited in some ways I want to use it. Ultimately I want it to be easier to find all the data by bringing up things with search.

Willmk
  • 13
  • 4

1 Answers1

0

I think I have a basic answer for you. However your sample sheet is not very much like your final sheet, with all of the tabs you've mentioned, so there is only the working concept that I can demonstrate here. With a really reresentative sample sheet, I could flesh out more details on how the links to multiple possible tabs would need to be built. See my sample tab, GK-Help Search, added to your sample sheet.

First, we do a query, in column B, to return the list of matching car parts.

=QUERY('Car Parts'!A2:A,"select A where upper(A) contains '"&UPPER(A2)&"' ",0)

For your production sheet, this would require all of the data tabs to be concatenated, in a vertically stacked array. Eg.

=QUERY({ 'doors'!A2:A;
         'hoods'!A2:A;
         'lights'!A2:A  },"select...")

Then the main formula is this, in C2:

=HYPERLINK("https://docs.google.com/spreadsheets/d/1WrImPYHhhMOOZbf-AE2sNs82xL-u8wWOW4IFel6RGcY/edit#gid=" &
    "0" & "&range=" &
    SUBSTITUTE(REGEXEXTRACT(CELL("address",INDIRECT("'Car Parts'!A" & MATCH(B2,'Car Parts'!A$2:A,0)+1)),"(\$.*)"),"$",""),
  CELL("address",INDIRECT("'Car Parts'!A" & MATCH(B2,'Car Parts'!A$2:A,0)+1)))

This does a lookup of each car part, to get the address of the cell. Then a dynamic HYPERLINK is built, using the URL of the spreadsheet, and the address of the cell. The element that is not fleshed in my demo out is how to build the "gid" part of the URL address, since you did not provide multiple sample tabs. But this is very possible.

Here is a previous answer on doing that last part.
how-to-insert-hyperlink-to-a-cell-in-google-sheet-using-formula

My sample sheet looks like the following:

enter image description here

halfer
  • 19,824
  • 17
  • 99
  • 186
kirkg13
  • 2,955
  • 1
  • 8
  • 12
  • The formula could use some cleanup, to add error checking, etc... but that can wait until it is upgraded to handle your production sheet configuration. – kirkg13 Dec 17 '20 at 18:44
  • I would love for you to check out my more flushed on version - needing input on multiple tabs using your method. https://docs.google.com/spreadsheets/d/1_gcyygG8IhVc-4mXf8n09IFG5ZIYuK_UxWxPLQ5av-k/edit#gid=1004865702 – Willmk Jan 13 '21 at 20:10
  • See my tab in your sheet - https://docs.google.com/spreadsheets/d/1_gcyygG8IhVc-4mXf8n09IFG5ZIYuK_UxWxPLQ5av-k/edit#gid=1396402456&range=A1 ...... I've got the selection from multiple tabs working in a way that you might like. But it would take me quite a while to "build" the link to the specific cell in the respective tab - Bumper, Door or Hood, etc. See my notes there. – kirkg13 Jan 14 '21 at 05:23
  • Thank you! I've incorporated the work you did into my work, I can see how that would take a lot of work to do and i'm fine with just showing the info. – Willmk Jan 18 '21 at 15:13
  • FYI, as an enhancement, the second dropdown I've shown, "Select Car Parts Keyword" could be modified to only list items that are available for the Make/Model selected in the first dropdown. Search for something like "dynamic dependent dropdown" to find web tutorials on this. Eg. https://stackoverflow.com/questions/21744547/how-do-you-do-dynamic-dependent-drop-downs-in-google-sheets/35456798#35456798 or https://sheetswithmaxmakhrov.wordpress.com/2018/01/02/create-2d-dependent-dropdown-list/ – kirkg13 Jan 18 '21 at 15:38
  • Thanks for the info again. If I wanted to incorporate a 3rd search for the Col3, how would I add that to the formula? I will be adding tires with their sizes (255/70/16 for ex) and want to also be able to search that instead of just the make/model and part type – Willmk Jan 22 '21 at 17:17
  • I added a 3rd search, in tab 3rdSearch. If you set it to "----", it is ignored, and the other two search filters apply. Otherwise, you could have it pick a wheel size, or look for specific tire sizes if you want. At the moment, it just lists everything in the Tires tab. You'd need to modify it, as you see fit. And make a copy of this sheet, if you want to learn a great way to do those dependent dropdowns I mentioned earlier. https://docs.google.com/spreadsheets/d/11FCDfHQWQQdxc0tT9yPqM8yYj6ar_sMz4dh_nqfNSVc/edit?usp=sharing – kirkg13 Jan 23 '21 at 01:49