Hi There,
I am trying to create a resource availability file in Excel.
I have input data such as the following:
<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>
What I am trying to do, is to display the data in the following manner:
<colgroup><col><col span="2"><col><col span="12"></colgroup><tbody>
</tbody>
The formula I have at present appears to only work when I go to the 2nd index, anything beyond that and I get incorrect values. I am not sure if the formula I am using is making this more complex than is needed.
I also have not been able to figure out where to add/how to add if it is a holiday or training.
The formula I have at present is:
=OR(IF(AND(INDEX($A$2:$C$6,SMALL(IF($A$2:$A$6=$A$14,ROW($A$2:$A$6)),ROW(1:1))-1,3)<=C14,INDEX($A$2:$D$6,SMALL(IF($A$2:$A$6=$A$14,ROW($A$2:$A$6)),ROW(1:1))-1,4)>=C14),"True","False"),IF(AND(INDEX($A$2:$C$6,SMALL(IF($A$2:$A$6=$A$14,ROW($A$2:$A$6)),ROW(2:2))-1,3)<=C14,INDEX($A$2:$D$6,SMALL(IF($A$2:$A$6=$A$14,ROW($A$2:$A$6)),ROW(2:2))-1,4)>=C14),"True","False"))
So the data, I posted at the beginning of this thread is in cells A2 to C6.
The name of the "Person A", highlighted in Red above is in cell A14
The dates along the top of the 2nd piece of data I posted is in cell C14.
I am trying to get the formula to read the date along the top and check if the person in column A has training of a holiday on that date. I have built up the above formula from a between I was originally using to check if the date was between these 2 dates. I essentially am trying to figure out how I can use a between to check multiple rows.
I previously used vlookup, but realized Index had to be used due to needing the formula to read multiple lines.
Any help would be much appreciated.
Thanks.
I am trying to create a resource availability file in Excel.
I have input data such as the following:
| Type | Start Date | End Date | |
| Person A | Holiday | 01/05/2015 | 02/05/2015 |
| Person B | Training | 30/04/2014 | 03/05/2015 |
| Person B | Holiday | 08/05/2015 | 09/05/2015 |
| Person A | Holiday | 04/05/2015 | 07/05/2015 |
| Person A | Training | 09/04/2015 | 10/05/2015 |
<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>
What I am trying to do, is to display the data in the following manner:
| 28/04/2015 | 29/04/2015 | 30/04/2015 | 01/05/2015 | 02/05/2015 | 03/05/2015 | 04/05/2015 | 05/05/2015 | 06/05/2015 | 07/05/2015 | 08/05/2015 | 09/05/2015 | 10/05/2015 | 11/05/2015 | 12/05/2015 | |
| Person A | Holiday | Holiday | Holiday | Holiday | Holiday | Holiday | Training | ||||||||
| Person B | Training | Training | Training | Training | Holiday | Holiday |
<colgroup><col><col span="2"><col><col span="12"></colgroup><tbody>
</tbody>
The formula I have at present appears to only work when I go to the 2nd index, anything beyond that and I get incorrect values. I am not sure if the formula I am using is making this more complex than is needed.
I also have not been able to figure out where to add/how to add if it is a holiday or training.
The formula I have at present is:
=OR(IF(AND(INDEX($A$2:$C$6,SMALL(IF($A$2:$A$6=$A$14,ROW($A$2:$A$6)),ROW(1:1))-1,3)<=C14,INDEX($A$2:$D$6,SMALL(IF($A$2:$A$6=$A$14,ROW($A$2:$A$6)),ROW(1:1))-1,4)>=C14),"True","False"),IF(AND(INDEX($A$2:$C$6,SMALL(IF($A$2:$A$6=$A$14,ROW($A$2:$A$6)),ROW(2:2))-1,3)<=C14,INDEX($A$2:$D$6,SMALL(IF($A$2:$A$6=$A$14,ROW($A$2:$A$6)),ROW(2:2))-1,4)>=C14),"True","False"))
So the data, I posted at the beginning of this thread is in cells A2 to C6.
The name of the "Person A", highlighted in Red above is in cell A14
The dates along the top of the 2nd piece of data I posted is in cell C14.
I am trying to get the formula to read the date along the top and check if the person in column A has training of a holiday on that date. I have built up the above formula from a between I was originally using to check if the date was between these 2 dates. I essentially am trying to figure out how I can use a between to check multiple rows.
I previously used vlookup, but realized Index had to be used due to needing the formula to read multiple lines.
Any help would be much appreciated.
Thanks.
