Form value populated based on previous selection

[Sarah_Keown]

Hi, I need a form value to populate based on the previous selection from a dropdown.

I have the following 3 columns; Product - Product Code - Product Description within an excel sheet. The Product column is populated through the concatenation of Product Code and Description. You can see an example of this below.

I have multiple sheets with the same column information, except the product groups are different. In the form I select my Product Group which then allows me to select from a list of Products associated to that group; the dropdown shows the Product column from the spreadsheet i.e. the concatenated values.

Underneath the product group and product dropdown items in the form, I want to populate the following ‘Product Code’ form value with the product code associated with the product they have chosen. So, much like I have filtered the product groups and products, I want the product code for that selected row to appear in the form. For example, if a user selects ‘HD1/10W-Hep20 Pb Staright Connector WT 10’ the product code ‘HD1/10W’ will appear in the product code form input.

I have attempted this using various SELECT() and FILTER() statements, but because the product groups are across various sheets in excel I cannot find an expression that is universal to the situation; I have to refer to a specific dataset(sheet) in excel but this process must work across all groups of products.

If there are any possible approaches to this, I would be appreciative of any advice.

Thanks