I am utilizing dependent drop down lists with an indirect function. The first drop down list is Column I, the second drop down list is Column J, and the indirect formula is running in Column N through Column Y. Everything is working great, except I cannot get the data validation in the 2nd drop down list to carry down correctly through column J. As you can see in the screenshot, I have J12 selected but the data validation is still referencing the cells in Row 11, not 12. I am having to go down through each cell and manually change the data validation reference to the correct row. It will NOT carry correctly when I do the handy little "auto-fill" function that sheets and excel use. Any one have any advice on this?

Shows data validation issue


1 Answer 1


Replace =$N$11:$Y$11 with =N11:Y11. See absolute and relative references.

  • THANK YOU!!!!!! I know enough about excel and sheets to get some great stuff done but not all of the little nitty gritty working pieces like that! Whew! I'm so glad it was an easy fix!
    – skuban145
    Jun 1 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.