Make data validation display values from one column and store values from another

Hi All,

In a sheet I have a named range with 2 columns, named "StoreViews".

ID Description
1 Dutch B2B
2 German B2B
3 English B2B
4 Dutch Consumer
5 German Consumer
6 English Consumer
7 Dutch
8 German
9 English
10 All Stores

I would like to reference this named range in a data validation on a second sheet.
However... I want to display the descriptions and store the ID's of the named range.

How do I do that?
If I simply set the data validation to the named range, I get values from both columns.
Any pointers are greatly appreciated!

Thanks.

Could you please upload your sample file with desired result ?

Regards.

Ok, attached here is an example file.

The actual data validation in this file now, is on the description column of the named range.
So this is not the desired end result.

I have mimicked what it's supposed to do in a screenshot.
DataValidation.jpg

I need the Number-values from the ID column to perform future actions, but these numbers alone do not make sense to the user.
He needs to select a value based on the descriptions.


I hope my intend is now clear.

To get ID

=INDEX(ID,MATCH(Workfile!C2,Beschrijving,0))

"ID" & "Beschrijving" are named ranges

Hi John,

I've been trying to get this formula to work inside my data validation, but it doesn't.
And if I put the formula in column C of the Workfile and set the data validation on the named Range "Description" I get cross reference errors.
I must be missing something.
Can you please upload my original file with this formula working?

If I'm not wrong, you want to display text in dropdown and after selection the result is a numbere related to selected text.
I think you need a vba for your task.

Please Login or Register  to view this content.

Regards.


Note : the vba code is just adhoc , I'm not so good in VBA.

It does not work inside Data Validation - not least as you will get a circular reference.

And you cannot have both a Formula and Value in a cell (only using VBA which will overwrite the formula anyway)

Formula has to be in its own cell e.g. D2.

Your VBA script solved it Menem!
Thanks a lot!

If you ok for '2 steps' of dropdown this formula may help

C2
=IF(ISBLANK(C2),'Named Ranges'!$B$2:$B$11,INDEX('Named Ranges'!$A$2:$A$11,MATCH(C2,'Named Ranges'!$B$2:$B$11,0)))

Regards.

Hi Menem,

I prefer to use your script. It does however have a small issue.
If I clear cell content in column C I get an error message.
How do I handle the situation where a populated cell get's cleared, fires the script because the sheet changed, but the new value (nothing) is not present in the "Storeviews" named range?

Solved it myself.

The script is now this:

HTML Code: 

Sorry to say, I can't do VBA properly.
Please wait for another member to help.

Regards.

I thought if I added the extra line of code at the top errors would be prevented. (If Intersect(target, Range("C:C")) Is Nothing Then Exit Sub)

HTML Code: 

This extra line prevents errors from occurring when I clear data in column C,
but if I clear all contents from A, B and C i get the error again.
error.jpg

Can someone help me out please?

Try

Please Login or Register  to view this content.