• 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

    Hui...

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

Conditional Formatting to identify relevant changes in data validation options

B

BobDobalina

New Member
So, there seems to be quite a bit of stuff regarding data validation and conditional formatting, but I cannot piece together an answer to this question. If I have missed something, please let me know.


I have a column that uses the offset function and data validation to choose 1 of 7 lists for the dropdown menu depending on sales volume. That works great.


However, if I update the sales volume - and it gets pushed/dropped into a new grouping, the dropdown choices might change. Is there a way (I was thinking conditional formatting) to highlight in some fashion when the previous selection from the dropdown menu no longer matches one of the selections from the new drop down menu?


Thanks in advance!


- Bob
 
https://dl.dropbox.com/u/27285421/Sample1_R2.xlsx Here's simplified example of what I am trying to do. Thanks to Narayank for helping me with the formula to get the right drop down menu in Column E.


Now I want to highlight the cells in column E that no longer match the dropdown menu options when I change the volume in column B.
 
Hi Bob ,


What you want is possible except for one problem ; the options "N/A" and "other" are present in every drop-down menu ; so , if the existing selection was one of these , and the amount is changed , then the corresponding cell will not be formatted. Is this OK with you ?


Check out the file here :


https://docs.google.com/open?id=0B0KMpuzr3MTVNVF2ZURZdUxaYXc


I have not yet put in the CF for the column E cells ; this is a fairly easy matter given that the values in column G will be #N/A! whenever the changed volume puts it in a different slot.


Narayan
 
Yeah in that case - it would be ok. I would only rarely use the n/a and other options anyway.


Long day at work. I'll take a look over the weekend. AS always, I appreciate the help!
 
I suppose there is no simple way to determine once the sales volume is changed, that a different drop down menu will appear when liking on a cell in that column and that the existing choice no longer will be available? The error alerts only pop up once you go there and type in an invalid choice.


Anyway, I'm sure you have found the best way Narayank. I'm just frustrated with myself for not understanding this better.


Thanks!
 
Hi Bob ,


You know that a volume change will result in a different drop-down menu if the corresponding cell in column G shows #N/A!.


You don't have to wait to click on the drop-down.


Narayan
 
You must log in or register to reply here.
Back
Top