Hi, I am compiling dependent drop down list, where I want the information to be on a seperate sheet than the final drop down list. My information is as follows, on Worksheet called "Information":
Gender (Male/Female/Team); [formula to select: Data Validation/=Gender]
column A, Belt Colour (White/Yellow/Orange/Green to Blue/Purple to Red/Brown to Black);[formula to select: Data Validation/=Beltto]
column B to H, Divison (this is based on gender/age/colour of belt and then also "kata" or "kumite",
Columns I - O is the selection of Gender; Category (children/cadet etc) & Belt colour (belt colour drop down list in column O);
Then I have a formula in P1, =IF(ISBLANK(O1),"",LEFT(ADDRESS(1,MATCH(O1,A:A,0)+1,4,1),1)&":"&LEFT(ADDRESS(1,MATCH(O1,A:A,0)+1,4,1),1)) ]
In R1, the validation is =INDIRECT(P1). This gives me then the correct drop down list for the criteria selected.
BUT, now I want this drop down list (in R1) to actually appear on another worksheet,called "Drawsheet".
If I enter the same Validation as I did on the Information worksheet, it returns the value as on the "Drawsheet" and not "Information" sheet. How can I get the "final" drop down to be selected in antoher sheet?
Gender (Male/Female/Team); [formula to select: Data Validation/=Gender]
column A, Belt Colour (White/Yellow/Orange/Green to Blue/Purple to Red/Brown to Black);[formula to select: Data Validation/=Beltto]
column B to H, Divison (this is based on gender/age/colour of belt and then also "kata" or "kumite",
Columns I - O is the selection of Gender; Category (children/cadet etc) & Belt colour (belt colour drop down list in column O);
Then I have a formula in P1, =IF(ISBLANK(O1),"",LEFT(ADDRESS(1,MATCH(O1,A:A,0)+1,4,1),1)&":"&LEFT(ADDRESS(1,MATCH(O1,A:A,0)+1,4,1),1)) ]
In R1, the validation is =INDIRECT(P1). This gives me then the correct drop down list for the criteria selected.
BUT, now I want this drop down list (in R1) to actually appear on another worksheet,called "Drawsheet".
If I enter the same Validation as I did on the Information worksheet, it returns the value as on the "Drawsheet" and not "Information" sheet. How can I get the "final" drop down to be selected in antoher sheet?