Hey everyone! I’m working on a Google Sheets project and need some help. I’ve got two sheets: one for products and another for parts. The products sheet has a list of items with their details. Each product has a unique name in column A. The parts sheet shows all the components for these products, with product names in column B.
I want to enable a feature where clicking a product name in the products sheet not only takes you to the parts sheet but also filters the list to show only the parts for that specific product. I can use HYPERLINK to jump between sheets, but I haven’t figured out how to trigger the filter automatically.
Any tips or ideas using Google Scripts or another approach would be really appreciated. Let me know if you need more details. Thanks!
Having worked extensively with Google Sheets, I can suggest an alternative approach that doesn’t require complex scripting. You can use a combination of INDIRECT and FILTER functions to achieve this dynamically. In your products sheet, create a cell with a data validation dropdown list of all product names. Then, in the parts sheet, use a formula like:
=FILTER(A:C, B:B=INDIRECT(“Products!A1”))
Replace ‘A1’ with the cell containing your dropdown. This will automatically filter the parts list based on the selected product. While it doesn’t provide automatic navigation, it offers real-time filtering without the need for custom scripts. You can then use simple HYPERLINKS between sheets for navigation.
hey alex, i’ve got a neat trick for ya. try using the QUERY function in ur parts sheet. something like:
=QUERY(A:C, “SELECT * WHERE B = '” & Products!A1 & “'”)
then just link to that cell from ur products sheet. it’ll auto-filter when u click. no fancy scripting needed! lemme know if u need more help
I encountered a similar challenge recently and resolved it by developing a custom function in Google Apps Script. Instead of simply using the HYPERLINK function, I created a function that accepts the product name as input, retrieves the active spreadsheet, and navigates to the parts sheet. Once there, the function applies a filter based on the given product name to display only the relevant parts. This approach provided a smooth transition and enhanced user experience without confusing manual steps. For those less comfortable with scripting, using the QUERY or FILTER functions provides another way to dynamically update and link specific cell ranges.