I'm creating a formula that; IF B:B = [email protected], D:D = 25-09-2019, H:H = Holiday
then in 2nd sheet mark H on TEST3 Wed 25th. IF Other = O, Business/Travel = B/T, Course = C.
=IF(AND(B:B="[email protected]",D:D="23-09-2019",H:H="Business/Travel"),"B/T",IF(AND(B:B=" [email protected]",D:D="25-09-2019",H:H="Holiday"),"H",""))
When I apply the same formula in all the cells under TEST3 I find the result only in the E4 cell (which corresponds row 4 of the 1st table, I expect all the cells to be populated). Dont know whats going wrong.
| Approver | Requested by | Today's date | First day of absence | Total number of days | If half day, please specify: | First day back in the office | Please mark as appropriate | Total number of holidays booked so far this year including above |
| Test | [email protected] | 20-09-2019 | 25-09-2019 | 1 | 26-09-2019 | Holiday | 22 | |
| Test | [email protected] | 20-09-2019 | 25-09-2019 | 1 | 26-09-2019 | Holiday | 22 | |
| Test | [email protected] | 20-09-2019 | 25-09-2019 | 1 | 26-09-2019 | Holiday | 22 | |
| Test | [email protected] | 20-09-2019 | 25-09-2019 | 1 | 26-09-2019 | Holiday | 22 | |
| Test | [email protected] | 20-09-2019 | 25-09-2019 | 1 | 26-09-2019 | Other | 22 | |
| Test | [email protected] | 20-09-2019 | 23-09-2019 | 1 | 24-09-2019 | Courses | 15 | |
| Test | [email protected] | 20-09-2019 | 25-09-2019 | 3.5 | ["AM"] | 26-09-2019 | Business/Travel | 11 |
| Test | [email protected] | 20-09-2019 | 23-09-2019 | 3.5 | ["AM"] | 26-09-2019 | Business/Travel | 10 |
| Test | [email protected] | 20-09-2019 | 23-09-2019 | 3.5 | ["AM"] | 26-09-2019 | Business/Travel | 9 |
| Test | [email protected] | 20-09-2019 | 23-09-2019 | 3.5 | ["AM"] | 26-09-2019 | Business/Travel | 8 |
| Test | [email protected] | 23-09-2019 | 24-09-2019 | 3 | 27/09/2019 | Holiday | 12 |
| TEST1 | TEST2 | TEST3 | TEST4 | TEST5 | TEST6 | TEST7 | TEST8 | TEST9 | TEST10 | TEST11 | ||
| Mon | 2 | |||||||||||
| Tues | 3 | |||||||||||
| Wed | 4 | H | ||||||||||
| Thurs | 5 | |||||||||||
| Fri | 6 | |||||||||||
| Mon | 9 | |||||||||||
| Tues | 10 | |||||||||||
| Wed | 11 | |||||||||||
| Thurs | 12 | |||||||||||
| Fri | 13 | |||||||||||
| Mon | 16 | |||||||||||
| Tues | 17 | |||||||||||
| Wed | 18 | |||||||||||
| Thurs | 19 | |||||||||||
| Fri | 20 | |||||||||||
| Mon | 23 | |||||||||||
| Tues | 24 | |||||||||||
| Wed | 25 | |||||||||||
| Thurs | 26 | |||||||||||
| Fri | 27 | |||||||||||
| Mon | 30 |
Welcome to our forum!
The IF function doesn't operate over a whole column, it checks a single cell. Therefore your formula probably should be more like:
=IF(AND(B2="[email protected]",D2="23-09-2019",H2="Business/Travel"),"B/T",IF(AND(B2="[email protected]",D2="25-09-2019",H2="Holiday"),"H",""))
Then you copy the formula to the other rows you want to test.
That said, I'm not sure there aren't more issues with the formula and it's difficult to tell without seeing your file. Please upload a sample Excel file so we can see your question in context.
Mynda
