"OR" function?

**adb_bliu** · 08-31-2011, 09:37 PM

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!

**teylyn** · 08-31-2011, 11:44 PM

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?

**adb_bliu** · 09-01-2011, 02:15 AM

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?

**teylyn** · 09-01-2011, 05:50 PM

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,