Google Sheets - problem with INDIRECT formula with data validation

[jedidia]

Hi guys,
I am trying to use INDIRECT with dependant drop downs.
I have one worksheet, column R is drop down with two choices (Customer, Courier), Column S should display a drop down of choices varying - depending on the choice made in Column R.
So I created dependent drop downs: in another tab I created two lists Customer with its choices and Courier with its choices. Customer and Courier became names for the name ranges. Then I created a data validation drop down with the ranges Customer and Courier in column R.
So far so good. I created then formula: =INDIRECT(R200;TRUE) - where R200 is the cell number where the data validation drop down [Customer, Courier) begins. Then in columns S I created another data validation using the range that covers all the options resulting from the INDIRECT formula. Until now EVERYTHING works fine. When I choose from dropdown in column R, then in column S I have the right set of choices. But it doesn't work if I want to use it in the next cells down the columns... If I copy the data validation down, the INDIRECT referencing doesn't work, because the list of options is stored only in one range of cells, meaning it always reads only for R200 cell (starting point). How can I make it work in all cells under the R200?

 

[jasonb75]

I would have expected it to copy down relative as there are no $ symbols in the formula. Try using =INDIRECT("RC18",FALSE) instead.

Given that sheets is working differently to how we would expect in excel, it might be necessary for you to provide a link to a sanitised copy (confidential details removed or fictionalised) of your sheet so that we can see first hand what is happening.

 

[jedidia]

I would have expected it to copy down relative as there are no $ symbols in the formula. Try using =INDIRECT("RC18",FALSE) instead.

Given that sheets is working differently to how we would expect in excel, it might be necessary for you to provide a link to a sanitised copy (confidential details removed or fictionalised) of your sheet so that we can see first hand what is happening.

=INDIRECT("R200",FALSE) doesn't resolve the issue unfortunately. As I am trying this out on a working copy of an actual sheet, I could share it to you, I just need the information to which e-mail address.

 

[jasonb75]

Any samples related to questions have to be posted in the public forum, the rules say that we're not allowed to take it private. This is why I suggested a sanitised copy of your sheet (create copy then delete anything not needed as part of the question and fictionalise anything confidential that needs to remain).

 

[jedidia]

Alright, so can you access it now from here?

Testing workbook

 

[jasonb75]

Have you seen an article anywhere that tells you how to do dependent dropdowns on google sheets?

All of the articles that I have seen relate to excel and looking at it in detail, it appears that what you want to do can only be done in excel, not in google.

I'll keep looking but I don't think that it will be possible.

 

[jedidia]

Thank you for looking into this.
Yes, I did use a tutorial for google sheets, here: How to Create a Dependent Drop Down List in Google Sheets
The problem is, that it works for a single entry but not for multiple cells in a column and this is exactly what I need (and it can't be in done excel unfortunately because it has to be a shared file) I am open to using any other functions of google sheet to execute my desired results.

 

[jasonb75]

It appears that it will only work with a single cell in google sheets, not a range of cells.

In excel, you would enter the INDIRECT formula into the popup window (where you have 'Criteria' 'List from range' in sheets), not into a cell in the sheet itself. Sheets doesn't support this method (it actually doesn't allow you to specify the range by using a formula which is how it is done in excel) which makes the task much more difficult than it should be.

From what I can see there is no way that it can be done in sheets. If there is then it would be by rewriting the dropdown on the fly using script. I don't know if that is even possible, for something like that you would need to ask in a dedicated sheets forum as the script code used for sheets is a different language to the vba used by excel and it is unlikely that anyone here will understand it well enough to write the code that you need.

Edit:- following up on my reply and contradicting what I have already said, this may be what you need. I haven't read the article in detail but scrolling to the end, the latter screen captures appear to be doing what you are trying to achieve.

Multi-Row Dynamic Dependent Drop-Down List in Google Sheets

We can create a multi-row dynamic dependent drop-down list in Google Sheets without using Google Apps Script.

infoinspired.com

 

[jedidia]

Thank you so much, I will look into this link today later on and will give you feedback here!