2

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

halfer
  • 19,824
  • 17
  • 99
  • 186
dantevn5
  • 37
  • 9

1 Answers1

2

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)))
player0
  • 124,011
  • 12
  • 67
  • 124
  • So I tried it and I got this `Array result was not expanded because it would overwrite data in E3.` It's not letting me drag it down :/ – dantevn5 Jul 28 '20 at 07:55
  • thats the idea. no need to drag down anything. arrayformula will autocomplete it for all rows. only E2 cell should be containing this arrayformula – player0 Jul 28 '20 at 08:15
  • hmm I don't get it, the dropdowns only appear in the first row, once I complete the first row what am I supposed to do? https://imgur.com/a/Yl0YV2J – dantevn5 Jul 28 '20 at 08:36
  • see: https://docs.google.com/spreadsheets/d/19Oft1HimMNkuIZ0Qvh9b18kqsX_41Uzx/edit#gid=1706153928 – player0 Jul 28 '20 at 08:41
  • I removed the "Koalicija column" and literally copied what you did, and it still doesn't show up. Oh well, it obviously works so thank you, I'll try to figure it out somehow. You're amazing dude. – dantevn5 Jul 28 '20 at 08:47
  • So I just noticed that in your sheet copy, the 2nd dropdown always contains static options, no matter what option I pick in the first dropdown. I realize this is definitely hard, so if you know of a on click function that simply exports this to a sheet/text file that could work too, way easier than doing this complicated stuff. – dantevn5 Jul 28 '20 at 09:02