Script logic in google spreadsheet?

Question

Google Sheets

I'm trying to solve the problem of generating an automatic specification. There are databases in Google tables for equipment in different sections.

The specification has columns: equipment name, article number, manufacturer, etc.

For example, in cell A1 there is a drop-down list with options for electrical panels. Further, as we have chosen a shield, data from the database should be loaded into the columns article, manufacturer, etc.

It seems like they did it through the IF function with nesting a lot of values.

Everything would be fine, but only the problem arises that when replenishing the list of used cabinets, you have to manually add these IFs.

Is it possible to write some kind of script or what combinations of functions can help to implement the correct work of the table. Or at least what are the ideas of the logic of the process, maybe there are suitable functions or the logic of the script, what should it be? Thought it could be done through a cycle.

That is, the IF function is repeated while the value of cell A1 is equal to one of the values ​​of Range A1:A10. I don't understand what the cycle will look like then.

Answer 1

It's easier. A "reference" is created separately - options are loaded there using the FILTER() formula, depending on the value selected in the first column. And in the second column - a link to this "reference book" - as options for checking the data (drop-down list).
You change the data in the first column, the directory updates the values, new choices appear in the second column.

Answer 2

If I understand your question correctly, then you really need to create a separate directory. It should look like this:

Here, after the name, there should be the characteristics of the product you need. In the example, this is the article and manufacturer.
After that, in the cell to the right of the product selection, add this formula (the values ​​\u200b\u200bhere are for example, you will need to replace them with your own): =index('Reference'!B5:D ; MATCH(B3 ; 'Reference'!B5:B ; 0) ; 2)
Where:
'Reference'!B5:D is the range in the 'reference' sheet where all data about goods is stored
B3 - the address of the cell with the name of the goods in the specification. Where you have a
'Reference' drop-down list! B5:B - a column in the 'Reference' sheet with product names.
And the 2 at the end is the number of the column in the "reference" sheet from which you need the values. For example, if you put 2, then this will be the article, 3 - the supplier, etc.