Log In
Register
Home
New Member
September 25, 2019
Offline
I'm creating a formula that; IF B:B = Test3@test.co.uk, 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="Test3@test.co.uk",D:D="23-09-2019",H:H="Business/Travel"),"B/T",IF(AND(B:B=" Test3@test.co.uk",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 | Test1@test.co.uk | 20-09-2019 | 25-09-2019 | 1 | 26-09-2019 | Holiday | 22 | |
| Test | Test2@test.co.uk | 20-09-2019 | 25-09-2019 | 1 | 26-09-2019 | Holiday | 22 | |
| Test | Test3@test.co.uk | 20-09-2019 | 25-09-2019 | 1 | 26-09-2019 | Holiday | 22 | |
| Test | Test4@test.co.uk | 20-09-2019 | 25-09-2019 | 1 | 26-09-2019 | Holiday | 22 | |
| Test | Test5@test.co.uk | 20-09-2019 | 25-09-2019 | 1 | 26-09-2019 | Other | 22 | |
| Test | Test6@test.co.uk | 20-09-2019 | 23-09-2019 | 1 | 24-09-2019 | Courses | 15 | |
| Test | Test7@test.co.uk | 20-09-2019 | 25-09-2019 | 3.5 | ["AM"] | 26-09-2019 | Business/Travel | 11 |
| Test | Test8@test.co.uk | 20-09-2019 | 23-09-2019 | 3.5 | ["AM"] | 26-09-2019 | Business/Travel | 10 |
| Test | Test9@test.co.uk | 20-09-2019 | 23-09-2019 | 3.5 | ["AM"] | 26-09-2019 | Business/Travel | 9 |
| Test | Test10@test.co.uk | 20-09-2019 | 23-09-2019 | 3.5 | ["AM"] | 26-09-2019 | Business/Travel | 8 |
| Test | Test11@test.co.uk | 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 |
July 16, 2010
Offline
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="Test3@test.co.uk",D2="23-09-2019",H2="Business/Travel"),"B/T",IF(AND(B2="Test3@test.co.uk",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
1 Guest(s)
