+ Reply to Thread
Results 1 to 4 of 4

"OR" function?

  1. #1
    Registered User
    Join Date
    08-25-2011
    Location
    Manila
    MS-Off Ver
    Excel 2007
    Posts
    10

    "OR" function?

    Hi all, need help with a model I am building.

    Column 1 will have a drop menu that contains 10 values (think employee level, from Level 1 to 10).

    When a user selects a value from Column 1, I want Column 2 to automatically populate with the same value selected in Column 2.

    However, I also want to provide the user with the option of selecting another value in Column 2, if they choose so.

    So for instance, in Column 1, I've selected a Level 1 employee. So in Column 2, it automatically appears as Level 1 as well. But say I decide that in Column 2, I decide I want to force a Level 2 instead, so I manually drop the menu to select.

    Is this possible?

    Thanks!

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: "OR" function?

    Hello,

    Yes, but ...

    You can have a formula in column B along the lines of

    =if(A2<>"",A2,"")

    Copy down as far as required.

    AFTER that, add data validation for column B with the same list settings as for column A.

    Now the BUT: After the user has manually selected a value in B2, the formula to reference A2 is no longer there. So, if they then go and change A2 to another value, it will not reflect in B2.

    Another option would involve VBA code. Would you be comfortable with that?

  3. #3
    Registered User
    Join Date
    08-25-2011
    Location
    Manila
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: "OR" function?

    Hey this works extremely well, thanks.

    Okay but I do see your point about the "BUT"...in fact, there might very well be a scenario where we need to revert back, and I see that basically choosing the latter option erases the IF function completely.

    Is there a way to lock this or prevent it from happening?

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: "OR" function?

    Another option would be to have a Worksheet Change Event macro that monitors changes in column A. As soon as a cell in column A gets changed, the macro will copy the value to column B.

    Both column A and B have the same data validation setup.

    Select a value in column A and the same value will appear in column B (that's the macro at work. There is no formula in column B. The user can select a different value in column B. But when they edit column A again, the current value from column A will be copied to column B again.

    This is the code

    Please Login or Register  to view this content.
    See attached file for a working example. You need to enable macros. The code is in the Sheet1 module (right-click on Sheet1 and select View Code)

    cheers,
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1