Hi,
I have a workorder that looks like this:
<table style="border-collapse: collapse; width: 749pt;" width="978" border="0" cellpadding="0" cellspacing="0"><col style="width: 129pt;" width="172"> <col style="width: 20pt;" width="26" span="31"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl63" style="height: 13.5pt; width: 129pt;" width="172" height="18"> </td> <td class="xl83" style="width: 20pt;" width="26">01</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">02</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">03</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">04</td> <td class="xl86" style="border-left: medium none; width: 20pt;" width="26">05</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">06</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">07</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">08</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">09</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">10</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">11</td> <td class="xl86" style="border-left: medium none; width: 20pt;" width="26">12</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">13</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">14</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">15</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">16</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">17</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">18</td> <td class="xl86" style="border-left: medium none; width: 20pt;" width="26">19</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">20</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">21</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">22</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">23</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">24</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">25</td> <td class="xl86" style="border-left: medium none; width: 20pt;" width="26">26</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">27</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">28</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">29</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">30</td> <td class="xl87" style="border-left: medium none; width: 20pt;" width="26">31</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="border-top: medium none; height: 12.75pt;" height="17">Name 1
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">20</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">20</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">16</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">12</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">12</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">20</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">12</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">16</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">16</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">20</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">16</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">14</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">20</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt;" height="17">Name 2
</td> <td class="xl75" style="border-left: medium none;">20</td> <td class="xl76" style="border-left: medium none;">16</td> <td class="xl77" style="border-left: medium none;">X</td> <td class="xl77" style="border-left: medium none;">X</td> <td class="xl78" style="border-left: medium none;">20</td> <td class="xl77" style="border-left: medium none;">20</td> <td class="xl76" style="border-left: medium none;">20</td> <td class="xl76" style="border-left: medium none;">20</td> <td class="xl76" style="border-left: medium none;">20</td> <td class="xl77" style="border-left: medium none;">16</td> <td class="xl77" style="border-left: medium none;">X</td> <td class="xl78" style="border-left: medium none;">X</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl81" style="border-left: medium none;">16</td> <td class="xl79" style="border-left: medium none;">14</td> <td class="xl80" style="border-left: medium none;">X</td> <td class="xl80" style="border-left: medium none;">X</td> <td class="xl80" style="border-left: medium none;">X</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl79" style="border-left: medium none;">16</td> <td class="xl81" style="border-left: medium none;">20</td> <td class="xl79" style="border-left: medium none;">18</td> <td class="xl80" style="border-left: medium none;">12</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl82" style="border-left: medium none;">16</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt;" height="17">Name 3
</td> <td class="xl75" style="border-left: medium none;">X</td> <td class="xl76" style="border-left: medium none;">X</td> <td class="xl77" style="border-left: medium none;">20</td> <td class="xl77" style="border-left: medium none;">20</td> <td class="xl78" style="border-left: medium none;">20</td> <td class="xl77" style="border-left: medium none;">20</td> <td class="xl76" style="border-left: medium none;">18</td> <td class="xl76" style="border-left: medium none;">18</td> <td class="xl76" style="border-left: medium none;">X</td> <td class="xl77" style="border-left: medium none;">X</td> <td class="xl77" style="border-left: medium none;">20</td> <td class="xl78" style="border-left: medium none;">18</td> <td class="xl79" style="border-left: medium none;">14</td> <td class="xl80" style="border-left: medium none;">12</td> <td class="xl80" style="border-left: medium none;">18</td> <td class="xl80" style="border-left: medium none;">18</td> <td class="xl79" style="border-left: medium none;">16</td> <td class="xl79" style="border-left: medium none;">X</td> <td class="xl81" style="border-left: medium none;">X</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl79" style="border-left: medium none;">16</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl81" style="border-left: medium none;">20</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">16</td> <td class="xl80" style="border-left: medium none;">X</td> <td class="xl82" style="border-left: medium none;">X</td> </tr> </tbody></table>
The top row is days of the month. What I am trying to do is a 2 way lookup that returns all the names for the people that are off (X) when the user inputs the Day of the month. I managed to get the formula to work, but it either returns Name 1 all the time or all the names.
So what I want is the user to only change the date. In the column marked with an X it must return the names of everyone that is working an X.
The formula I am using is:
Names = Name1, Name2, Name3
Userdate = the date the user inputs
Days = the days of the month
Shift = the X column
Shift Allocated is the Array above showing what shift is worked by whom on what date
<table style="border-collapse: collapse; width: 187pt;" width="249" border="0" cellpadding="0" cellspacing="0"><col style="width: 187pt;" width="249"><tr style="height: 20.1pt;" height="26"> <td class="xl65" style="height: 20.1pt; width: 187pt;" width="249" height="26">=INDEX(Names,MATCH(UserDate,Days,0),MATCH(Shift,Shiftallocated,0))
</td> </tr></table>
I have a workorder that looks like this:
<table style="border-collapse: collapse; width: 749pt;" width="978" border="0" cellpadding="0" cellspacing="0"><col style="width: 129pt;" width="172"> <col style="width: 20pt;" width="26" span="31"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl63" style="height: 13.5pt; width: 129pt;" width="172" height="18"> </td> <td class="xl83" style="width: 20pt;" width="26">01</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">02</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">03</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">04</td> <td class="xl86" style="border-left: medium none; width: 20pt;" width="26">05</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">06</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">07</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">08</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">09</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">10</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">11</td> <td class="xl86" style="border-left: medium none; width: 20pt;" width="26">12</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">13</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">14</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">15</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">16</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">17</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">18</td> <td class="xl86" style="border-left: medium none; width: 20pt;" width="26">19</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">20</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">21</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">22</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">23</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">24</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">25</td> <td class="xl86" style="border-left: medium none; width: 20pt;" width="26">26</td> <td class="xl85" style="border-left: medium none; width: 20pt;" width="26">27</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">28</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">29</td> <td class="xl84" style="border-left: medium none; width: 20pt;" width="26">30</td> <td class="xl87" style="border-left: medium none; width: 20pt;" width="26">31</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="border-top: medium none; height: 12.75pt;" height="17">Name 1
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">20</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">20</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">16</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">12</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">12</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">20</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">18</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">12</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">16</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">16</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">20</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">16</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">14</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">20</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt;" height="17">Name 2
</td> <td class="xl75" style="border-left: medium none;">20</td> <td class="xl76" style="border-left: medium none;">16</td> <td class="xl77" style="border-left: medium none;">X</td> <td class="xl77" style="border-left: medium none;">X</td> <td class="xl78" style="border-left: medium none;">20</td> <td class="xl77" style="border-left: medium none;">20</td> <td class="xl76" style="border-left: medium none;">20</td> <td class="xl76" style="border-left: medium none;">20</td> <td class="xl76" style="border-left: medium none;">20</td> <td class="xl77" style="border-left: medium none;">16</td> <td class="xl77" style="border-left: medium none;">X</td> <td class="xl78" style="border-left: medium none;">X</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl81" style="border-left: medium none;">16</td> <td class="xl79" style="border-left: medium none;">14</td> <td class="xl80" style="border-left: medium none;">X</td> <td class="xl80" style="border-left: medium none;">X</td> <td class="xl80" style="border-left: medium none;">X</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl79" style="border-left: medium none;">16</td> <td class="xl81" style="border-left: medium none;">20</td> <td class="xl79" style="border-left: medium none;">18</td> <td class="xl80" style="border-left: medium none;">12</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl82" style="border-left: medium none;">16</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt;" height="17">Name 3
</td> <td class="xl75" style="border-left: medium none;">X</td> <td class="xl76" style="border-left: medium none;">X</td> <td class="xl77" style="border-left: medium none;">20</td> <td class="xl77" style="border-left: medium none;">20</td> <td class="xl78" style="border-left: medium none;">20</td> <td class="xl77" style="border-left: medium none;">20</td> <td class="xl76" style="border-left: medium none;">18</td> <td class="xl76" style="border-left: medium none;">18</td> <td class="xl76" style="border-left: medium none;">X</td> <td class="xl77" style="border-left: medium none;">X</td> <td class="xl77" style="border-left: medium none;">20</td> <td class="xl78" style="border-left: medium none;">18</td> <td class="xl79" style="border-left: medium none;">14</td> <td class="xl80" style="border-left: medium none;">12</td> <td class="xl80" style="border-left: medium none;">18</td> <td class="xl80" style="border-left: medium none;">18</td> <td class="xl79" style="border-left: medium none;">16</td> <td class="xl79" style="border-left: medium none;">X</td> <td class="xl81" style="border-left: medium none;">X</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl79" style="border-left: medium none;">16</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl81" style="border-left: medium none;">20</td> <td class="xl79" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">20</td> <td class="xl80" style="border-left: medium none;">16</td> <td class="xl80" style="border-left: medium none;">X</td> <td class="xl82" style="border-left: medium none;">X</td> </tr> </tbody></table>
The top row is days of the month. What I am trying to do is a 2 way lookup that returns all the names for the people that are off (X) when the user inputs the Day of the month. I managed to get the formula to work, but it either returns Name 1 all the time or all the names.
So what I want is the user to only change the date. In the column marked with an X it must return the names of everyone that is working an X.
The formula I am using is:
Names = Name1, Name2, Name3
Userdate = the date the user inputs
Days = the days of the month
Shift = the X column
Shift Allocated is the Array above showing what shift is worked by whom on what date
<table style="border-collapse: collapse; width: 187pt;" width="249" border="0" cellpadding="0" cellspacing="0"><col style="width: 187pt;" width="249"><tr style="height: 20.1pt;" height="26"> <td class="xl65" style="height: 20.1pt; width: 187pt;" width="249" height="26">=INDEX(Names,MATCH(UserDate,Days,0),MATCH(Shift,Shiftallocated,0))
</td> </tr></table>
