Needing Formula for Table IF AND OR DATE

[fatcookie]

I am having an issue with writing the formula for the following.

IF A equals a certain DATE of the year (for example the 1st of every month) then B is "JACK" AND IF B is "JACK" then C= 80.00

1st - Jack = 80.00
2nd - Tammy = 20.00
3rd - Lisa = 60.00
4th - Laurie = 500.00
5th - Christina = 100.00

This would have to be for the entire worksheet so anytime a date is entered B is automatically "name" (depending on date) then C is automatically the amount.

Hope that makes sense.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD]01/01/2013[/TD]
[TD]Jack[/TD]
[TD]80.00[/TD]
[/TR]
[TR]
[TD]01/02/2013[/TD]
[TD]Tammy[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD]01/03/2013[/TD]
[TD]Lisa[/TD]
[TD]60.00[/TD]
[/TR]
[TR]
[TD]01/04/2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/05/2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/01/2013[/TD]
[TD]Jack[/TD]
[TD]80.00[/TD]
[/TR]
[TR]
[TD]02/02/2013[/TD]
[TD]Tammy[/TD]
[TD]20.0[/TD]
[/TR]
[TR]
[TD]02/03/2013[/TD]
[TD]Lisa[/TD]
[TD]60.00[/TD]
[/TR]
[TR]
[TD]02/04/2013[/TD]
[TD]Christina[/TD]
[TD]100.00[/TD]
[/TR]
[TR]
[TD]02/05/2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/01/2013[/TD]
[TD]Jack[/TD]
[TD]80.00[/TD]
[/TR]
[TR]
[TD]03/02/2013[/TD]
[TD]Tammy[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD]03/03/2013[/TD]
[TD]Lisa[/TD]
[TD]60.00[/TD]
[/TR]
[TR]
[TD]03/04/2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/05/2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06/10/2013[/TD]
[TD]Laurie[/TD]
[TD]500.00[/TD]
[/TR]
[TR]
[TD]02/02/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/03/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/04/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/05/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/01/2014[/TD]
[TD]Jack[/TD]
[TD]80.00[/TD]
[/TR]
[TR]
[TD]03/02/2014[/TD]
[TD]Tammy[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD]03/03/2014[/TD]
[TD]Lisa[/TD]
[TD]60.00[/TD]
[/TR]
[TR]
[TD]03/04/2014[/TD]
[TD]Christina[/TD]
[TD]100.00[/TD]
[/TR]
[TR]
[TD]03/05/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[bbott]

Welcome to the board.

You'll need two different formulas; one for column B and one for column C. Enter these in B2 and C2 respectively and copy down:

=IFERROR(CHOOSE(DAY(A2),"Jack","Tammy","Lisa","Laurie","Christina"),"")

=IFERROR(CHOOSE(DAY(A2),80,20,60,500,100),"")

 

[fatcookie]

Welcome to the board.

You'll need two different formulas; one for column B and one for column C. Enter these in B2 and C2 respectively and copy down:

=IFERROR(CHOOSE(DAY(A2),"Jack","Tammy","Lisa","Laurie","Christina"),"")

=IFERROR(CHOOSE(DAY(A2),80,20,60,500,100),"")

The names are not also in order. For example Jack may be the 11/01/2014, 12/01/2014, 01/01/2015...etc and Tammy is 11/15/2014, 12/15/2014, 01/15/2015....They will be on the same day of the month. So its not 1 through 5 but really 1-30/31 (days of the month). Am i making it more confusing? I also have Mary that needs to be once every week.

Basically i have the days listed and i have certain names that need to go on a specific day every month and if it that day and that person then it will be a certain amount every time. Someone will be a monthly person and others will be a weekly thing...like every Monday .

Thanks for taking the time with me!

[TABLE="width: 71"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]11/1/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/2/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/3/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/4/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/5/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/6/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/7/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/8/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/9/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/10/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/11/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/12/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/13/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/14/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/15/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/16/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/17/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/18/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/19/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/20/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/22/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/23/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/24/2014[/TD]
[/TR]
[TR]
[TD="align: right"]11/25/2014[/TD]
[/TR]
</tbody>[/TABLE]

 

[bbott]

So do you have a table with 31 days and who would be assigned to which day?

 

[fatcookie]

I have a table with January through December (365 days). I have a list of names and amounts that go with a specific day each month. What i don't know how to do is a formula to auto fill a persons name and amount to a specific date each month or manually enter the information each year.

 

[bbott]

Can you post a small sample of your table?

 

[fatcookie]

[TABLE="width: 407"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]12/28/2013[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]8500.00[/TD]
[/TR]
[TR]
[TD="align: right"]12/29/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8500.00[/TD]
[/TR]
[TR]
[TD="align: right"]12/30/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8500.00[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8500.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2014[/TD]
[TD]Ben[/TD]
[TD="align: right"]698.00[/TD]
[TD][/TD]
[TD="align: right"]7802.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7802.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2014[/TD]
[TD]TRANSFER[/TD]
[TD] [/TD]
[TD="align: right"]8374.00[/TD]
[TD="align: right"]16176.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/4/2014[/TD]
[TD]Jack[/TD]
[TD="align: right"]1000.00[/TD]
[TD][/TD]
[TD="align: right"]15176.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/5/2014[/TD]
[TD]Tammy[/TD]
[TD="align: right"]1000.00[/TD]
[TD][/TD]
[TD="align: right"]14176.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2014[/TD]
[TD]Laurie[/TD]
[TD="align: right"]1000.00[/TD]
[TD][/TD]
[TD="align: right"]13176.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2014[/TD]
[TD]Jackie[/TD]
[TD="align: right"]500.00[/TD]
[TD][/TD]
[TD="align: right"]12676.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2014[/TD]
[TD]Carrie[/TD]
[TD="align: right"]500.00[/TD]
[TD][/TD]
[TD="align: right"]12176.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/9/2014[/TD]
[TD]Christina[/TD]
[TD="align: right"]500.00[/TD]
[TD][/TD]
[TD="align: right"]11676.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2014[/TD]
[TD]TRANSFER[/TD]
[TD] [/TD]
[TD="align: right"]500.00[/TD]
[TD="align: right"]12176.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12176.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/12/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12176.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/13/2014[/TD]
[TD]Mond[/TD]
[TD="align: right"]200.00[/TD]
[TD][/TD]
[TD="align: right"]11976.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2014[/TD]
[TD]Charlie[/TD]
[TD="align: right"]200.00[/TD]
[TD][/TD]
[TD="align: right"]11776.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/15/2014[/TD]
[TD]Tabitha[/TD]
[TD="align: right"]200.00[/TD]
[TD][/TD]
[TD="align: right"]11576.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/16/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11576.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/17/2014[/TD]
[TD]TRANSFER[/TD]
[TD] [/TD]
[TD="align: right"]5412.00[/TD]
[TD="align: right"]16988.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16988.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/19/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16988.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/20/2014[/TD]
[TD]Betty[/TD]
[TD="align: right"]658.00[/TD]
[TD][/TD]
[TD="align: right"]16330.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16330.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16330.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/23/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16330.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/24/2014[/TD]
[TD]TRANSFER[/TD]
[TD] [/TD]
[TD="align: right"]491.00[/TD]
[TD="align: right"]16821.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/25/2014[/TD]
[TD]Brian[/TD]
[TD="align: right"]350.00[/TD]
[TD][/TD]
[TD="align: right"]16471.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2014[/TD]
[TD]Casey[/TD]
[TD="align: right"]1250.00[/TD]
[TD][/TD]
[TD="align: right"]15221.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/27/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15221.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/28/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15221.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/29/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15221.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/30/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15221.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2014[/TD]
[TD]TRANSFER[/TD]
[TD] [/TD]
[TD="align: right"]1483.00[/TD]
[TD="align: right"]16704.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2014[/TD]
[TD]Ben[/TD]
[TD="align: right"]698.00[/TD]
[TD][/TD]
[TD="align: right"]16006.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/2/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16006.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/3/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16006.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/4/2014[/TD]
[TD]Jack[/TD]
[TD="align: right"]1000.00[/TD]
[TD][/TD]
[TD="align: right"]15006.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/5/2014[/TD]
[TD]Tammy[/TD]
[TD="align: right"]1000.00[/TD]
[TD][/TD]
[TD="align: right"]14006.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/6/2014[/TD]
[TD]Laurie[/TD]
[TD="align: right"]1000.00[/TD]
[TD][/TD]
[TD="align: right"]13006.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/7/2014[/TD]
[TD]Jackie[/TD]
[TD="align: right"]500.00[/TD]
[TD][/TD]
[TD="align: right"]12506.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/7/2014[/TD]
[TD]TRANSFER[/TD]
[TD] [/TD]
[TD="align: right"]5081.00[/TD]
[TD="align: right"]17587.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/8/2014[/TD]
[TD]Carrie[/TD]
[TD="align: right"]500.00[/TD]
[TD][/TD]
[TD="align: right"]12006.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/9/2014[/TD]
[TD]Christina[/TD]
[TD="align: right"]500.00[/TD]
[TD][/TD]
[TD="align: right"]11506.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11506.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/11/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11506.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/12/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11506.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/13/2014[/TD]
[TD]Mond[/TD]
[TD="align: right"]200.00[/TD]
[TD][/TD]
[TD="align: right"]11306.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/14/2014[/TD]
[TD]Charlie[/TD]
[TD="align: right"]200.00[/TD]
[TD][/TD]
[TD="align: right"]11106.00[/TD]
[/TR]
[TR]
[TD="align: right"]2/14/2014[/TD]
[TD]TRANSFER[/TD]
[TD] [/TD]
[TD="align: right"]1608.15[/TD]
[TD="align: right"]12914.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/15/2014[/TD]
[TD]Tabitha[/TD]
[TD="align: right"]200.00[/TD]
[TD][/TD]
[TD="align: right"]12714.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/16/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12714.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/17/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12714.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/18/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12714.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/19/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12714.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/20/2014[/TD]
[TD]Betty[/TD]
[TD="align: right"]658.00[/TD]
[TD][/TD]
[TD="align: right"]12056.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2014[/TD]
[TD]TRANSFER[/TD]
[TD] [/TD]
[TD="align: right"]491.00[/TD]
[TD="align: right"]12547.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/22/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12547.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/23/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12547.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/24/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12547.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/25/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12547.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/26/2014[/TD]
[TD]Brian[/TD]
[TD="align: right"]350.00[/TD]
[TD][/TD]
[TD="align: right"]12197.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2014[/TD]
[TD]Casey[/TD]
[TD="align: right"]1250.00[/TD]
[TD][/TD]
[TD="align: right"]10947.15[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2014[/TD]
[TD]TRANSFER[/TD]
[TD] [/TD]
[TD="align: right"]8500.00[/TD]
[TD="align: right"]19447.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2014[/TD]
[TD]Ben[/TD]
[TD="align: right"]698.00[/TD]
[TD][/TD]
[TD="align: right"]18749.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/2/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18749.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/3/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18749.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/4/2014[/TD]
[TD]Jack[/TD]
[TD="align: right"]1000.00[/TD]
[TD][/TD]
[TD="align: right"]17749.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/5/2014[/TD]
[TD]Tammy[/TD]
[TD="align: right"]1000.00[/TD]
[TD][/TD]
[TD="align: right"]16749.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/6/2014[/TD]
[TD]Laurie[/TD]
[TD="align: right"]1000.00[/TD]
[TD][/TD]
[TD="align: right"]15749.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/7/2014[/TD]
[TD]Jackie[/TD]
[TD="align: right"]500.00[/TD]
[TD="align: right"]500.00[/TD]
[TD="align: right"]15749.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/8/2014[/TD]
[TD]Carrie[/TD]
[TD="align: right"]500.00[/TD]
[TD][/TD]
[TD="align: right"]15249.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/9/2014[/TD]
[TD]Christina[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15249.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/10/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15249.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/11/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15249.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/12/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15249.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/13/2014[/TD]
[TD]Mond[/TD]
[TD="align: right"]200.00[/TD]
[TD][/TD]
[TD="align: right"]15049.15[/TD]
[/TR]
[TR]
[TD="align: right"]3/14/2014[/TD]
[TD]TRANSFER[/TD]
[TD] [/TD]
[TD="align: right"]500.00[/TD]
[TD="align: right"]15549.15
[/TD]
[/TR]
</tbody>[/TABLE]


The people will almost always have the same amount one the same day per month. The TRANSFER is a weekly transaction every Friday.

 

[fatcookie]

Can you post a small sample of your table?

have i lost ya?