Dependent Data Validation Formula With RC[-1] Cell

Question

Title may be a bit confusing; but hopefully this explains better. I'm trying to create a table with two dependent data validation lists. Now I have figured out the formula for creating these, but I'm wondering if I could somehow add a condition where the indirect function looks to the cell that is directly to the left of the dependent cell. Where my data validation formula is as follows:

       =INDIRECT(SUBSTITUTE($E$17," ",""))

Though the $E$17 would point the the cell to the left-to cell beside it, instead. This would essentially save me from having to recreate the data validation formula over +400 times for each dependent drop-down cell in my table.

I'm not the greatest with Excel formulas, so any help would be greatly appreciated!

Hi Klesker, I would love to help you with your problem, I'm just a little confused about what you're asking for. Would something like this help? =INDIRECT(SUBSTITUTE(OFFSET($E$17,0,-1)," ","")) — I like Excel very much
Are you asking for a formula that looks up a cell based upon the first selected dropdown and then creates a dropdown list based upon what the cell to the immediate left contains? =INDIRECT(SUBSTITUTE((VLOOKUP($E:$17,$G$G:$H$H,2,0)),"","")) — I like Excel very much
Sorry, my explanation wasn't the greatest. I basically want to set-up my dependent data validation cell and associated formula so that it automatically points to the cell beside it (i.e., RC[-1]) instead of having to explicitly state said cell in the formula; which would get around the problem of having to copy and paste the formula to manually setup each row of independent and dependent cells. — Klesker
=INDIRECT(SUBSTITUTE(E17," ","")) The $ are what lock the cell reference. — I like Excel very much

Klesker Klesker · Accepted Answer · 2020-08-11T22:05:57

Figured it out, for anyone else that might stumble upon this thread with the same problem! I ended up changing the cell reference style in the Options -> Formulas section (check the R1C1 reference style box) and then using the following code:

      =INDIRECT(SUBSTITUTE(RC[-1]," ",""))

Dependent Data Validation Formula With RC[-1] Cell

1 Answers