Data Validation (List) too long

I am trying to create a drop down list that see's the entry to left of it and brings down the appropriate list.
My code is as follows:

=IF(A2="1 BCT",FBCTBNS,IF(A2="2 BCT",SBCTBNS,IF(A2="3 BCT",TBCTBNS,IF(A2="4 BCT",FORTHBCTBNS,IF(A2="212 FIRES",TOTFIRESBNS,IF(A2="15 SUS",FIFTEENSUSBNS,IF(A2="11 ADA",EADABNS,IF(A2="402 FA",FOSFABNS,IF(A2="1 CAB",CABBNS,IF(A2="OTHER",OTHERBNS,""))))))))))

This works just fine.

However, I need to add more to the code as my list to pick from has gotten longer. My problem is that the code is too long.
I have read from other sources that one can put the code simply in the workbook and name it then reverence back to it.
This does not work for me as when I put the code in the sheet, without it trying to create a list, it doesn't make sense. So if I try to reference back to it in list, it returns #VALUE.

Is there any kind of work around that I can do to accomplish more characters to a drop list in data validation?

Use CHOOSE.

See: http://www.techonthenet.com/excel/formulas/choose.php

Regards, TMS

Edit: another link with examples: http://www.myonlinetraininghub.com/e...hoose-function

try this

http://www.myonlinetraininghub.com/e...ependent-lists


or this

http://www.contextures.com/xlDataVal02.html

I believe you can start the Named Range name with an underscore (_). So you could have _1st_BCT.

To overcome the spaces in the name issue, you would need to use SUBSTITUTE and replace spaces with underscores.

If for example, your selection was 1st BCT in cell A1, then you'd use something like =INDIRECT("_" & SUBSTITUTE(A1, " ", "_")) and the name of the Named Range would be _1st_BCT.

All untested, but that's the theory. Not going to get tested without a sample workbook.

Regards, TMS

Clearly, Toonies has a better grasp of your requirements than I do. You provided a long nested IF statement ... I was trying to help make that more scalable using CHOOSE. It wasn't obvious to me that FBCTBNS, etc., were named ranges, nor that what you wanted was dependent drop down lists ... my bad.

I would urge you to follow Toonies' recommendation and update a sample workbook. That will make life easier for both of you.

On that note, I'll back off.

Regards, TMS

Maybe this technique can help and eliminate the need to use INDIRECT at all.

http://www.excelforum.com/showthread...43#post2845843

@JB

I get an error if I try to follow that link:


vBulletin Message
TMShucks, you do not have permission to access this page. This could be due to one of several reasons:

Your user account may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.


Why would that be?

Checking now as to that error.

@Versial - post your workbook and I'll show you how to apply this. This basically replaces the INDIRECT() methodology with a data validation formula that uses OFFSET() to find where the secondary list should start and stop on its own. You don't need to predefine a bunch of ranges.

I tried the &SUBSTITUTE in quick test, didn't work at first try but I don't have time to experiment with it right now. May try more later.

I have attached my sample workbook, you will see the main list is BDE, all the names in BDE (10 in this sample), have a corresponding sub-list. They are notable by BN's at the end of it. I have removed all the names for the ranges except for BDE and OTHER, as I don't know if they will play a role in how you can help.

I also have the list in $A14 for BDE and $B14 for what should be the corresponding BN list.

Book1.xlsx

I have been really surprised with how helpful this forum is and how quick the responses have been. Thank you guys for the help you have provided and in advance.

Also adding underscores allowed me to put in numbers and spaces, I did not know you could do this before, again thank you.

Thanks for the rep.

Regards, TMS

I know I'm late to the party, but I did promise a "no-indirect-solution". Here it is.

By putting all your lists on a separate page like shown, you can use a single dynamic named range "Titles" to make a list of the row1 values for your first DV Drop down. As you add more columns, the list expands itself.

Then on the SHeet1 B2 cell, all the plumbing is built into the DV formula itself. It spots which value in A2 was selected, finds it on the Lists sheet row1, then makes a DV list of all the other values in that same column.

=OFFSET(INDEX(Titles,MATCH($A2,Titles,0)),1,,COUNTA(OFFSET(INDEX(Titles,1),,MATCH($A2,Titles,0)-1,1000,))-1)

Make sure there is a value in A2 when you first enter that formula to avoid an irrelevant error message.

TFears,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Instead of using CONCATENATE use an ampersand (&) between the cell addresses, etc

This:

Formula:

CONCATENATE(Sheet1!A3," ",Sheet1!B3)

becomes this:

Formula:

Sheet1!A3&" "&Sheet1!B3

But note ...

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.