• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dependent Drop down list in seperate worksheets


New Member
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?

Luke M

Excel Ninja
While it sounds like a cool setup, I'm having trouble visualizing everything that's going on. I think the problem is at the end with the INDIRECT function, and you wanting to reference the correct drop down to use. Please correct if any of following is wrong.

=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)) ]

This formula is generating an output like "B:B"

Your INDIRECT function then takes that text string and gives you the actual range.

If above 2 statements are true, here's proposed solution.

Create a Named Range defined as something like this:


I may have gotten the sheet references mixed up. Formula as written would figure out where the value in O1 appears in col A on sheet Drawsheet, and then return the corresponding column from Information sheet. Is this on the right track, or am I completely off base?


New Member
Hi, your statements are correct, but I am having difficulty with the formula.

I have uploaded the file, it might make it easier to see. (the spreadsheet is far from finish, thus no 'finishing touches' added yet :))


(hope it works, first time I'm doing this upload thingy)

I want the drop down list to appear in Z53 on 'Drawsheet1 Kata'

The information is on Sheet1

Luke M

Excel Ninja
Afraid I can't access uploaded workbooks from my location, but someone else might be able to. I'll do my best as is.

The formula I gave would replace the two you already have. Named Ranges can be found under Insert - Name Define (2003) or under the Formula ribbon.

The formula itself works by taking an area (columns A:Z) and then accepting arguments stating what row and/or column to look at. Based on your previous formula, I was assuming that you wanted an entire column to be returned. So, assuming you have a named range called "MyVal" with the formula previous given, the data valdation dialogue would use a List source, with this in the formula box:


If that doesn't help, hopefully someone else who can access your workbook will chime in. =)


Excel Rōnin
Hi, Adell!

Picking up the thread because of Luke M's CASFFML issue, I have a few questions:

a) worksheet 'Drawsheet1 Kata'

- cell $O$1 is blank

- column A:A is blank too

b) Z53 cell drop down list

- what values do you expect to be retrieved?


@Luke M


I was actually missing your CASFFML handy feature :p



Excel Ninja
Hi ,

Can you download the following file and check if this is what you wanted ?




New Member

Like, I was maybe too tired last night, but just could not get your suggetion to work. :)

SirJB7, on the 'Drawsheet1 Kata' sheet, I want a drop down of all the option for examble, Yellow belt boys to appear in a drop down list. These "option" are on Sheet1. The aim at the end of the day is to make the final selection list shorter, based on gender; age (children/cadet/juniors etc) and belt colour.

Narayan, this is exactly what I want to achieve, thank you.

With this, I am now going to "populate" the formula further to make the final selection easier, based on criteria entered (male and female, old age groups and all belts).

Thank you,


Excel Rōnin
Hi, Adell!

Glad you solved it. Credit to NARAYANK991, I just asked something that appeared as evident, I guess.

Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.