Hi all,

I am new to Google Sheets, but I do have some knowledge of spreadsheets and would consider myself an "advanced beginner/early intermediate" Excel user. I have been wrestling with this for two days and can't figure out how to make it work, so I would really appreciate some tips!

The link to my workbook is at the bottom of this post.

I need each coloured section to have its own set of dependent drop downs, based on the drugs and doses commonly used for each part of the procedure. I need that same set of dropdowns to be repeated in each row of the relevant section; although the drop downs in the sections may have different information in them, they will all be set up the same way.

I am currently working on the blue section (I coloured the data for this section blue as well on the "database" tab):

The drop downs are set up as two sets of dependent dropdowns. Set #1 has concentration (column B) dependent on drug (column A). Set #2 has dose (column C) dependent on drug (column A). I have left them separate instead of making C dependent on B which is dependent on A because some of the drugs come in different concentrations and there are also different doses that can be given. The doses and concentrations are relevant to the drugs, but not to each other (I hope that makes sense).

The only coding language I have ever used is html (I'm old. I know.), so I would really like to avoid using appscript if possible, as if I ever need to change anything, I wouldn't know how to avoid breaking the code. I tried to figure out appscript before coming to bug you guys, but realized that it's going to take me a super long time to learn enough to do what I want here.

I found an article where the guy managed to do what I'm trying to do using arrayformula, but I can't get it to work. He also has his "messy data" (the hidden stuff that populates the dropdowns, I don't know what the actual name for that is) off to the side of his main sheet in his example. I would like mine to stay on the data sheet so that my main sheet stays clean as it will likely either be printed or saved digitally into a medical record and I don't want the "official" version to have any non-relevant info on it. The article I found is:

infoinspired.com/google-docs/spreadsheet/dynamic-dependent-drop-down-list-in-google-sheets/

and the link to his example sheet is: https://docs.google.com/spreadsheets/d/1xe15-OBAe183XlJTh_mPwo67CEeQXaCcKY3UloXA2oo/copy

​

The link to my workbook is:

https://docs.google.com/spreadsheets/d/1xLf1JuiStjp8gZjtz7Jhv4SgGKX09_U6U_olFirgH28/edit?usp=sharing

The first sheet is my main sheet and the second one is my data. Ignore the last two, they're for the next phase of this project.

​

Thank you so much for any advice you can offer!