CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
dantevn5
dantevn5

Reputation: 47

Dynamic dropdowns, INDIRECT, multiple rows

So I have this "Google Sheets App" in one row. It has 2 interactive dropdowns (Dropdown B options appear based on Dropdown A), and some fields which change based on the option. I finally got all that to work using ARRAYFORMULA(INDIRECT), VLOOKUP, and so on. And it all works well, for the first row.

However, I need many rows of that, so I select the entire first row, and extend it all the way down. However, now Dropdown B options are based on the Dropdown A option FROM THE FIRST ROW, not the row where I'm picking stuff currently. And I understand that my ARRAYFORMULA(INDIRECT) is linked that way, and I would have to delete the first row if I was going to pick something else from another row. What I want to know is if it's possible to go around that, basically new row = new options, just keep the first row as simple values, don't affect anything else? Or at least somehow export the data from the row with a single click, so I can delete it and start all over again for new data?

This would ideally be done in a click since my boss wants me to make a completely functioning enterprise software in Google Sheets!

Google Sheet: https://drive.google.com/file/d/1HRZsqKyIxD35dqCmCc75ldbtZeGvimKD/view?usp=sharing

Upvotes: 2

Views: 146

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFNA(
 IFERROR(VLOOKUP(D2:D, data!A1:B20,    2, 0), 
 IFERROR(VLOOKUP(D2:D, data!A21:B42,   2, 0),
 IFERROR(VLOOKUP(D2:D, data!A43:B54,   2, 0),
 IFERROR(VLOOKUP(D2:D, data!A55:B61,   2, 0),
 IFERROR(VLOOKUP(D2:D, data!A62:B94,   2, 0),
 IFERROR(VLOOKUP(D2:D, data!A95:B101,  2, 0),
         VLOOKUP(D2:D, data!A102:B139, 2, 0)))))))))

enter image description here

H2 would be:

=ARRAYFORMULA(IF(F2:F="",,VALUE(TEXT(G2:G-F2:F, "h:mm:ss"))*24*60*60))

and I2 would be:

=ARRAYFORMULA(IF(E2:E="",,IF(E2:E>40, "Paket unijeti rucno", E2:E*H2)))

Upvotes: 2

Related Questions