Hit a road block. I am trying to create a formula that will automatically list 'A' people that are coming available in a given month. I got the formula that will list all the 'A' people, but I hit a road block on the part where it will list them only if they are coming available in the month specified. Here is my example set:
Sheet 1
<tbody>
</tbody>
I want the names to automatically populate in the below table:
Sheet 2
<tbody>
</tbody>
I have only been able to write an INDEX formula to be able to list all the 'A' people, but I dont know how to make it list them conditional upon the date they are available.
=INDEX(SHEET1!A2:A7,SMALL(IF(SHEET2!B1=SHEET1!B2:B7,ROW(SHEET1!B2:B7)-MIN(ROW(SHEET1!B2:B7))+1,""),ROW(A1)))
Can anyone please help? Its almost like i need to fit in somewhere =IF(AND(SHEET1!C2:C7>=(Cell with 1/1/17),SHEET1!C2:C7<=(Cell with 1/31/17)),???????, "")
Can anyone please help?
Thanks,
Aaron
Sheet 1
Name | Type | Date Available |
Buddy | A | 1/1/17 |
Gator | B | 1/19/17 |
Aaron | C | 1/15/17 |
Eric | C | 4/5/17 |
Ted | C | 5/5/17 |
Butch | A | 1/8/17 |
<tbody>
</tbody>
I want the names to automatically populate in the below table:
Sheet 2
January | A | B | C |
1 | (name) | (name) | (name) |
2 | (name) | ||
3 | |||
4 | |||
5 |
<tbody>
</tbody>
I have only been able to write an INDEX formula to be able to list all the 'A' people, but I dont know how to make it list them conditional upon the date they are available.
=INDEX(SHEET1!A2:A7,SMALL(IF(SHEET2!B1=SHEET1!B2:B7,ROW(SHEET1!B2:B7)-MIN(ROW(SHEET1!B2:B7))+1,""),ROW(A1)))
Can anyone please help? Its almost like i need to fit in somewhere =IF(AND(SHEET1!C2:C7>=(Cell with 1/1/17),SHEET1!C2:C7<=(Cell with 1/31/17)),???????, "")
Can anyone please help?
Thanks,
Aaron