Auto Populate the Contents of a Cell Based upon the Results of 2 Other Cells

[ChristineJJohn]

Hi All!!!

I’ll try to keep the background on this as brief as possible.

I have new matters that come in all day long through a Network system/software and I’m notified by email as they come in. I divvy each new matter out to my Team Members on a daily basis and I run a report twice a week.

Each Team member is assigned an ID Number:
Moe is identified as 1
Larry is identified as 2
Curly is identified as 3
Shemp is identified as 4

Some customers have Team members assigned to them, others don’t.

In any case, I keep a separate spreadsheet to make sure that when I divvy out the matters daily, I disseminate the unassigned matters evenly so that come report time, each Team member has as close to an equal number of matters assigned to them as I can get them.

What I’d like to do is have columns E, F, G and H automatically populate depending upon the Team member assigned in columns C or D.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Matter #[/TD]
[TD="align: center"]Customer Name[/TD]
[TD="align: center"]Pre-Assigned Team ID No.[/TD]
[TD="align: center"]Team ID No.[/TD]
[TD="align: center"]Moe[/TD]
[TD="align: center"]Larry[/TD]
[TD="align: center"]Curly[/TD]
[TD="align: center"]Shemp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[TD]It's a Wonderful Day[/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]456[/TD]
[TD]Thank you in Advance[/TD]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]987[/TD]
[TD]For Your Assistance[/TD]
[TD][/TD]
[TD="align: center"]2[/TD]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Columns I, J, K, and L keep a running total from day to day.

Columns C and D have drop-downs with options 1 through 4 to choose from

For example, if I choose 1 at C2, I want E2 to auto populate with the number 1. Or, if I choose 4 at C3, I want H3 to auto populate with a 1. Or if I choose 2 at D4, F4 will auto populate with a 1, etc.

Any suggestions?

Thank you in advance for your assistance in this matter!!!!

 

[Stubble Kid]

Try putting the following formulae in cols E,F,G, and H. it assumes assignment in col D takes precedence over the default assignment in col C.

E2: =IF(D2=1,1,IF(AND(D2="",C2=1),1,""))

F2: =IF(D2=2,1,IF(AND(D2="",C2=2),1,""))

G2: =IF(D2=3,1,IF(AND(D2="",C2=3),1,""))

H2: =IF(D2=4,1,IF(AND(D2="",C2=4),1,""))

 

[ChristineJJohn]

Worked like a charm!!!! Thank you so very much, Stubble Kid!!!! I truly appreciate your help!!!!

 

[ChristineJJohn]

I hope I'm not imposing here, but I hoped I could figure this out, but just can seem to....

What would the formula be if I were to add an additional column? In other words, I need to insert a column with the new C1 to read "Team ID No. Requested" while still maintaining "Pre-Assigned Team ID No." and "Team ID No." (the new D1 and E1, respectively).

Thank you in advance for any help you may be able to provide!!!!!

 

[Stubble Kid]

Assuming "Team ID No." [col E] takes ultimate precedence (ie supervisor's overriding choice), and "Team ID No. Requested" [col C] is a preference that otherwise overrides the default "Pre-Assigned Team ID No." [col D] then try the logic of the IF statements shown below.

F2 (Moe): =IF(E2=1,1,IF(AND(E2="",C2=1),1,IF(AND(E2="",C2="",D2=1),1,"")))

and these nested IF functions can be reduced to the following single IF function:

=IF(OR(E2=1,AND(E2="",C2=1),AND(E2="",C2="",D2=1)),1,"")


G2 (Larry): =IF(OR(E2=2,AND(E2="",C2=2),AND(E2="",C2="",D2=2)),1,"")

H2 (Curly): =IF(OR(E2=3,AND(E2="",C2=3),AND(E2="",C2="",D2=3)),1,"")

I2 (Shemp): =IF(OR(E2=4,AND(E2="",C2=4),AND(E2="",C2="",D2=4)),1,"")

 

[ChristineJJohn]

You are a Rock Star!!!!! Thank you so very much!!!!!!!!!!!!!