Translate string to use with validation list

[daolo]

Hi!

A1 contains a list with quite long text strings (i.e. "4/1.5 Mbit/s ADSL") and depending on choice there, I want a different drop-down list in B1.

I've used the following formula (in B1):

=IF(A1="";empty;INDIRECT("RT"&(LOOKUP(A1;C1:C5;D1:D5))))

where empty is "error list" with no choices, and my lists of choices for B1 are named "RT"+ a number depending of choice in A1.

I obviously did a table with the text strings (C1:C5) and made an adjacent list with a coresponding smaller numer, i.e. 1-5, so the string would return me RT3 for example.
However, the lookup doesn't work they way I wanted. I'm supposed to sort in descending order, still it returns errors and can't find text strings.

Is there any other way I could solve this issue? Change lookup-string, or use some other command?

Thanks!

[JBeaucaire]

When the text strings are this complicated, I, too, would do the lookup table approach to this problem.

DV NOTES:
1) You don't want to name your DV lists with names that match actual cell references. For instance, RT1 is an actual cell ref in Excel 2007, so I'd change those for now.

2) Truth is, you don't even need the indirect. If you're going to use a lookup table, the second column can simply have the full DV list name in it, yes?

3) In DV validation formulas, you don't need to specify the false or "empty" parameter. If the formula results in false/error, you get no DV list by default.

=========

If you still continue to have problems after making the adjustments based on the notes above, click GO ADVANCED and use the paperclip icon to post up your workbook. I'll take a look.

[rwgrietveld]

Indirect needs a string and interprets that as a range (reference)

You do not show what RT is, but it could be a column. This is a bit far to the right, but it could be.

Nevertheless I have adjusted it a bit, to not overcomplicate things and came up with:

Please Login or Register  to view this content.

[JBeaucaire]

Now I'm confused. If Ricardo's suggestion actually matches what you're after, this isn't a Validation List question. A Validation List is a list of options that appear when you click on a cell and a dropdown of options appear.

If you're looking to simply return one value from a lookup table based on a value in A1, that would just be a standard LOOKUP(), or VLOOKUP() or INDEX/MATCH.

So which is it? A single value returned automatically, or a changing "list of options" in B based on the values in A?

[daolo]

Now I'm confused. If Ricardo's suggestion actually matches what you're after, this isn't a Validation List question. A Validation List is a list of options that appear when you click on a cell and a dropdown of options appear.

If you're looking to simply return one value from a lookup table based on a value in A1, that would just be a standard LOOKUP(), or VLOOKUP() or INDEX/MATCH.

So which is it? A single value returned automatically, or a changing "list of options" in B based on the values in A?

You interpreted it right Beaucaire.
It's a validation list.

Good point, no need for me to use the RT prefix any longer. Just trying =LOOKUP in my validation list causes a problem though.. .The corresponding cell next to the long string of text isn't treated as a list, eventhough it has the same name as one of my list (old RT3 for example).
How do I solve that?
Anyway, with the original code with INDIRECT string, it still should work.
I didn't really get it, how do I have to sort to avoid the errors? Sometimes I return the wrong corresponding shortened text, sometimes nothing at all. The lookup somehow fails.

Anyway, I'm providing a simplified example...

[JBeaucaire]

In B7, the DV formula should be:

=INDIRECT(LOOKUP(A7,A1:A5,B1:B5))

However, is you want to be able to use an UNSORTED list of values in your list, use this instead:

=INDIRECT(VLOOKUP(A7,A1:B5,2,FALSE))

[daolo]

In B7, the DV formula should be:

=INDIRECT(LOOKUP(A7,A1:A5,B1:B5))

However, is you want to be able to use an UNSORTED list of values in your list, use this instead:

=INDIRECT(VLOOKUP(A7,A1:B5,2,FALSE))

Great, I'll try it friday. I assume that I can use a name of a list instead of a range? Will try that too.
Again, thanks.

[JBeaucaire]

Yes, you can use named ranges in the DV formulas, too.

==========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)