missdeannamarie
New Member
- Joined
- Mar 31, 2014
- Messages
- 25
Hello!! I am hoping you excel genius' can help a girl out!!! I have tried to explain below my situation. Please offer any help you think you can! Much appreciated.
TAB 1: Data is entered manually in cells B2:J3
<tbody>
</tbody>
TAB 2: This is where I want to enter the formula. I want this to act as a resource tool, that will show when each employee (CC) is scheduled for a project. I have one formula that works to tell me if an employee is double booked, but i want to add to this.
=IF(COUNTIF(TAB1!B2:B3,"*"&$A$2&"*")=1,"Z",IF(COUNTIF(TAB1!B2:B3,"*"&$A$2&"*")>1,"X",""))
Where Z = Booked once, X = Booked more than once
I want to add to this the ability to code the booked one as its location (AA) form TAB 1. This is where I need help!
I was trying something like this, but couldn't get it to work:
=IF(AND(B57:B58="PC",B57:B58="KW"),"V", "")
Once I have the letters returned, I then conditional format based on that for color coding.
<tbody>
</tbody>
Hope this makes sense!! Thanks a lot!!
TAB 1: Data is entered manually in cells B2:J3
The data in the cells is as follows:
AA: BB: CC, CC
Where AA is the Location of the Project, BB is the Tool, and CC are the employee's assigned to the project.
A | B | C | D | E | F | G | H | I | J | |
1 | PROJECT | 6/1/17 | 6/2/17 | 6/3/17 | 6/4/17 | 6/5/17 | 6/6/17 | 6/7/17 | 6/8/17 | 6/9/17 |
2 | P1 | PC: F3: KW,MG | PC: F3: KW,MG | PC: F3: KW,MG | PC: F3: KW,MG | PC: F3: KW,MG | PC: F3: KW,MG | PC: F3: KW,MG | ||
3 | P2 | CS: F1: KW,DL | CS: F1: KW,DL | CS: F1: KW,DL | CS: F1: KW,DL | CS: F1: KW,DL |
<tbody>
</tbody>
TAB 2: This is where I want to enter the formula. I want this to act as a resource tool, that will show when each employee (CC) is scheduled for a project. I have one formula that works to tell me if an employee is double booked, but i want to add to this.
=IF(COUNTIF(TAB1!B2:B3,"*"&$A$2&"*")=1,"Z",IF(COUNTIF(TAB1!B2:B3,"*"&$A$2&"*")>1,"X",""))
Where Z = Booked once, X = Booked more than once
I want to add to this the ability to code the booked one as its location (AA) form TAB 1. This is where I need help!
I was trying something like this, but couldn't get it to work:
=IF(AND(B57:B58="PC",B57:B58="KW"),"V", "")
Once I have the letters returned, I then conditional format based on that for color coding.
A | B | C | D | E | F | G | H | I | J | |
1 | EMPLOYEE | 6/1/17 | 6/2/17 | 6/3/17 | 6/4/17 | 6/5/17 | 6/6/17 | 6/7/17 | 6/8/17 | 6/9/17 |
2 | KW | |||||||||
3 | MG | |||||||||
4 | DL |
<tbody>
</tbody>
Hope this makes sense!! Thanks a lot!!