# auto populate a field based on other fields

S

#### sam

I have an excel sheet where I want to auto populate a field (that has
specific values listed in a drop down menu) based on two other fields (both
are dropdown menus with specific values)

For Eg.:
there are 3 fields A, B, C and all these three fields have specific values
which we can select through a drop down menu. Now, Lets say values in the
fields are as follows:

A: 1, 2, 3, 4, 5
B: 1, 2, 3, 4, 5
C: a, b, c

So now, If I select '1' for A and '1' for B then I DONT want any thing to
populate in C and leave it blank.
If I select '1' for A and '2' for B then I want 'a' to be populated in C
If I select '2' for A and '1' for B then I want 'b' to be populated in C
If I select '1' for A and '1' for B then I want 'c' to be populated in C

Basically,
If the value in field A is smaller then field B then I want 'a' to be
populated in C.
If the value in field B is smaller then field A then I want 'b' to be
populated in C.
If the value in field A and B are equal then I want 'c' to be populated in C.

Hope I made it clear enough.

J

#### Jacob Skaria

The below formula will do

In C1
=IF(A1<B1,"a",IF(A1=B1,"c","b"))

Further to handle blank entries
=IF(COUNT(A1:B1),IF(A1<B1,"a",IF(A1=B1,"c","b")),"")

If this post helps click Yes

S

#### sam

Hi Jacob,

the values I gave in were just an example. There are no numeric values in
any fields.

The concept that i explained is true, but the values are somewhat similar to
displayed as below:

A: Hello, What, Going, There
B: Did, Jolly, Cool, Tomorrow
C: Just, Did

so lets say for eg, If Hello and Did are selected, C will remain blanck
If Hello, Jolly are selected, C will populate to Jusst
If What and Did are selected, C will populate to Did

And so on.. I hope it is clearer now?

J

#### Jacob Skaria

Suppose in Sheet2 Col A Col B ColC you have the below data starting from Row1.
ColA ColB ColC
Hello Did Just
What Jolly Did
Going Cool
There Tomorrow

In Sheet1 C1 enter the below formula.. Try changing the entries in A1 and B1

=IF(MATCH(A1,Sheet2!\$A\$1:\$A\$10,0)=MATCH(B1,Sheet2!\$B\$1:\$B\$10,0),"",IF(MATCH(A1,Sheet2!\$A\$1:\$A\$10,0)<MATCH(B1,Sheet2!\$B\$1:\$B\$10,0),Sheet2!C1,Sheet2!C2))

If this post helps click Yes