Data validation list - restrict selection of 1 option in list (dependent)

[STUARTXL]

Hi all,

In cell A1 I have a data validation list where a user selects a 'primary currency' e.g. "USD", "GBP", "EUR" etc.

In cell B1 I want the user to select a 'secondary currency' using the same data validation list, but quite obviously the user should be prevented from making the same currency selection as in A1.

Is there any way to set up the data validation in cell B1 such that it allows users to see the exact same list (including the 'primary currency' in cell A1), but prevents the user from selecting the same currency as in cell A1?

Creating a dynamic data validation list is not an option since these selections recur on hundreds of rows below and hence would necessitate the creation of a dynamic list for each row, which is not an option.

Regards,

[Glenn Kennedy]

It's easy to exclude the primary choice completely. I am not sure if it's possible to "see" it in column B, but not select it.

[STUARTXL]

Really? I thought it would be the other way round i.e. more difficult to omit it completely than to include it and prevent selection, hence my suggestion.

Omitting it completely is far more ideal. Please can you assist?

[STUARTXL]

Bump no response

[AliGW]

Administrative Note:

We don't expect you to bump threads sooner than 24 hours after your last post or more than once a day - it has been only just over three hours since you last posted. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

[STUARTXL]

Dear AliGW,

Thank you for your message, although with the presumptuous undertones I would have appreciated a more private message, which is a courtesy I shall be extending to you soon enough.

[Glenn Kennedy]

Hi. I haven't been around much recently.

Try this out. There may be an "elephant in the room", however. How many ROWS do you want to apply this to?

If it's what you want, I will explain. If it's not... then a bit more head-scratching will be required!!

[FDibbins]

Stuart, FWIW, had I seen such a quick "bump" as yours, I would have responded in the exact same manner. All forums have rules and ours is no exception - all members are expected to abide by them.

Thanks for the understanding

[kvsrinivasamurthy]

Worksheet event is used

Please Login or Register  to view this content.

To paste the code
Right click o sheet tab--> View Code.
VB window opens
Paste the code.
Close VB widow.