How to do data validation on dependent lists with references from another sheet?

  • Hi all,


    I have yet another question regarding dependent lists. I have currently made a dependent list formula in data validation, where it restricts what users can choose from (eg: Only allow user to choose American countries from American region).


    I am using this data validation formula:

    Code
    =OFFSET($A$28,1,MATCH($B$14,Regions2,0)-1,COUNTA(OFFSET($A$28,1,MATCH($B$14,Regions2,0)-1,20,1)),1)


    This formula however, only works if the references i made from (eg title cell: $A$28) are from the same sheet.
    I would like to use this formula in another sheet of the same workbook (with reference to another worksheet like this: Settings!$A$28), but i am not sure how.


    Is there anyone that could help me with this issue? Thanks :)

  • Hi all,


    Sorry already got this solved haha :)


    If anyone wants to know what I did to solve it, I just made a "list" for the cell i want to reference from another sheet, hence bypassing data validation's "no other sheet reference" rule.


    Have a good day everyone!

  • Hi,


    For all future readers ... thanks for sharing your solution ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!