Drop down list with conditionnal value based on cells value

[Warluck]

Bonjour,

I have a problem getting excel to do what I want it to do ...

Here is the scenario :

I have 4 cells, A1 to A4

Cell A1, drop down list with two value (value1 and value2)
Cell A2, drop down list with many values (values irrelevant for the scenario)
Cell A3, drop down list with many values (values irrelevant for the scenario)
Cell A4, drop down list with many values (values irrelevant for the scenario)

Now, I want to be able to choose between "value1" and "value2" for cell A1, but, if after I did I fill all three cells A2, A3 and A4 I want it to default to "value2". If there is less than three value selected for A2, A3 and A4 nothing happen and value of cell A1 remains as choosen.

I'm not sure I'm clear but I'm sure I can't do it by my own at this point and I need your help.

Thanks in advance for your help.

[Richard Buttrey]

Hi,

You can't use a drop down to fill a cell with a constant value and then expect that cell to change dependent on some other cells.
You could put an IF formula in A1 which tests for A2:A4 (but it sounds as though this is not quite what you want), or you could use a macro.

Probably best if you upload a workbook

[Warluck]

Ok, lets say I do it an other way.

Cell A2, drop down list with many values (values irrelevant for the scenario)
Cell A3, drop down list with many values (values irrelevant for the scenario)
Cell A4, drop down list with many values (values irrelevant for the scenario)

Cell A5 display nothing if 1 or less of these three cells is filled
Cell A5 display value1 if more than 1 cell is filled but not all three
Cell A4 display value2 if all three are filled

This seems to some kind of nested IF but I can't find how to nest IF and use local variable at the same time.

[Richard Buttrey]

As I said, upload a workbook. And add the results you expect to see clearly identifying which cells are inputs and which are to be derived according to your rule.

[Warluck]

Ok, I uploaded a spreadsheet, hope it did work.

[Warluck]

It's been a long evening but worth it...

=SI(NB.VIDE(C13:E13)=0;"Fiole";SI(NB.VIDE(C13:E13)=1;"Ampoule";""))

aka

=IF(COUNTBLANK(C13:E13)=0;"VALUE1";IF(COUNTBLANK(C13:E13)=1;"VALUE2";""))

Thanks to Microsoft that make French Excel incompatible with English programming code ...

[mahaveersomani]

STILL it is going to confusing me dear....will you please upload a sample file in which you have one problem part and second is solution part..


not with value field etc fill some data please........so that i can help you
and tell me do you need a macro for this or excel formula?


Regards
CA Mahaveer Somani

[Warluck]

Thanks Mahaveersomani, my reply, just before yours was the answer to my question.

Basicaly, I wanted to be able to define the value of a cell based on non empty cell in a define selection which, basically do, the code I've shared with you.

[mahaveersomani]

ok...thats nice


cheers.....