I need to filter a schedule

dzoomz · Oct 30, 2010

Well:

I have a monthly schedule of 50 persons 7 different work groups with different rotating work weeks and shifts (M, Morning)(D, Day)(N, Night)(O, Off).
From the schedule I have to filter each day to get the people that work each shift and cut and paste to a daily prevision list to know how is working and where.
My idea is to have in a monthly folder with a spread sheet with the schedule and 31 other sheets that retrieve the daily info needed from the schedule for printing.
I have all ready have a daily prevision list linked to daily sign in sheets and total work hrs list but I can’t figure out how to automatically filter and send the list from my schedule to a different work sheet (Prevision list).

Thank you very much if you can help.
Email address removed by Moderator - refer Posting Rules

Derek Brown · Oct 31, 2010

Hello and welcome to The Board.
Have a look at the following post where I describe a method to "filter" data to another worksheet according to a time value in a column (Column F in the exmple). However, Column G in the example could be setting the value 1 or 0 against any column of data (not just 'time' as in the example). In fact it does not have to be 1 or 0 - it could be any value you want and that would be the value that you would use as the 'key' on a worksheet to contain the 'filtered' data.
It looks complicated but is not - just follow the example to see how it works.
If you do have a problem following it, just update this post with each of your column headings and an example of what you want to filter on - I guess that it may be the day of the month (1, 2, 3 ....31) - and I will give you an updated example.
http://www.mrexcel.com/forum/showthread.php?t=406664

dzoomz · Oct 31, 2010

thank you
 I’ve been watching a lot of exel magic tricks but can’t find anything similar to what I think I need.
[FONT=&quot]Gong to give this a good try. It is very slowly sinking into the grey matter in and on top of my head.[/FONT]

dzoomz · Nov 9, 2010

I’m back and haven’t been able to figure out what I have to put where and where I have to put what. So let me explain this in a different way.
On sheet 1 I have my schedule. Column A2 down names.
Columns B1– AG1 days of the month (1 – 31) and each column day a letter representing the persons shift (M-morning, E-evening, and N-night).
<table class="MsoNormalTable" style="width: 153.5pt; border-collapse: collapse; margin-left: 5.25pt; margin-right: 5.25pt;" width="256" align="left" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 6.4pt;"> <td style="width: 35.6pt; border: 1pt solid windowtext; padding: 0cm 3.5pt; height: 6.4pt;" valign="bottom" width="59" nowrap="nowrap">
</td> <td style="width: 14.1pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.4pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> <td style="width: 14.1pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.4pt;" valign="bottom" width="24" nowrap="nowrap"> T
</td> <td style="width: 14.4pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.4pt;" valign="bottom" width="24" nowrap="nowrap"> W
</td> <td style="width: 16.55pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.4pt;" valign="bottom" width="28" nowrap="nowrap"> TH
</td> <td style="width: 14.1pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.4pt;" valign="bottom" width="24" nowrap="nowrap"> F
</td> <td style="width: 14.1pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.4pt;" valign="bottom" width="24" nowrap="nowrap"> S
</td> <td style="width: 16.45pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.4pt;" valign="bottom" width="27" nowrap="nowrap"> SU
</td> <td style="width: 14.1pt; border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.4pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> </tr> <tr style="height: 7.2pt;"> <td style="width: 35.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 7.2pt;" valign="bottom" width="59" nowrap="nowrap"> NAME
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7.2pt;" valign="bottom" width="24" nowrap="nowrap"> 1
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7.2pt;" valign="bottom" width="24" nowrap="nowrap"> 2
</td> <td style="width: 14.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7.2pt;" valign="bottom" width="24" nowrap="nowrap"> 3
</td> <td style="width: 16.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7.2pt;" valign="bottom" width="28" nowrap="nowrap"> 4
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7.2pt;" valign="bottom" width="24" nowrap="nowrap"> 5
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7.2pt;" valign="bottom" width="24" nowrap="nowrap"> 6
</td> <td style="width: 16.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7.2pt;" valign="bottom" width="27" nowrap="nowrap"> 7
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7.2pt;" valign="bottom" width="24" nowrap="nowrap"> 8
</td> </tr> <tr style="height: 6.6pt;"> <td style="width: 35.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 6.6pt;" valign="bottom" width="59" nowrap="nowrap"> JOHN
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.6pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.6pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> <td style="width: 14.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 6.6pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 16.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 6.6pt;" valign="bottom" width="28" nowrap="nowrap"> L
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.6pt;" valign="bottom" width="24" nowrap="nowrap"> E
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.6pt;" valign="bottom" width="24" nowrap="nowrap"> E
</td> <td style="width: 16.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.6pt;" valign="bottom" width="27" nowrap="nowrap"> E
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.6pt;" valign="bottom" width="24" nowrap="nowrap"> E
</td> </tr> <tr style="height: 5.95pt;"> <td style="width: 35.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 5.95pt;" valign="bottom" width="59" nowrap="nowrap"> BOB
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 5.95pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 5.95pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> <td style="width: 14.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 5.95pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> <td style="width: 16.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 5.95pt;" valign="bottom" width="28" nowrap="nowrap"> M
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 5.95pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 5.95pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 16.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 5.95pt;" valign="bottom" width="27" nowrap="nowrap"> E
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 5.95pt;" valign="bottom" width="24" nowrap="nowrap"> E
</td> </tr> <tr style="height: 6.8pt;"> <td style="width: 35.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 6.8pt;" valign="bottom" width="59" nowrap="nowrap"> FRED
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 6.8pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 6.8pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 14.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.8pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> <td style="width: 16.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.8pt;" valign="bottom" width="28" nowrap="nowrap"> M
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.8pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.8pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> <td style="width: 16.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 6.8pt;" valign="bottom" width="27" nowrap="nowrap"> L
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 6.8pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> </tr> <tr style="height: 6.2pt;"> <td style="width: 35.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 6.2pt;" valign="bottom" width="59" nowrap="nowrap"> MIKE
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.2pt;" valign="bottom" width="24" nowrap="nowrap"> N
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.2pt;" valign="bottom" width="24" nowrap="nowrap"> N
</td> <td style="width: 14.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 6.2pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 16.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 6.2pt;" valign="bottom" width="28" nowrap="nowrap"> L
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.2pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.2pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> <td style="width: 16.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.2pt;" valign="bottom" width="27" nowrap="nowrap"> M
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 6.2pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> </tr> <tr style="height: 7pt;"> <td style="width: 35.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 7pt;" valign="bottom" width="59" nowrap="nowrap"> DAVID
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7pt;" valign="bottom" width="24" nowrap="nowrap"> N
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7pt;" valign="bottom" width="24" nowrap="nowrap"> N
</td> <td style="width: 14.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7pt;" valign="bottom" width="24" nowrap="nowrap"> N
</td> <td style="width: 16.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7pt;" valign="bottom" width="28" nowrap="nowrap"> N
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 7pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 7pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 16.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7pt;" valign="bottom" width="27" nowrap="nowrap"> M
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 7pt;" valign="bottom" width="24" nowrap="nowrap"> M
</td> </tr> <tr style="height: 2.7pt;"> <td style="width: 35.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 2.7pt;" valign="bottom" width="59" nowrap="nowrap"> TOD
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 2.7pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 2.7pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 14.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 2.7pt;" valign="bottom" width="24" nowrap="nowrap"> N
</td> <td style="width: 16.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 2.7pt;" valign="bottom" width="28" nowrap="nowrap"> N
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 2.7pt;" valign="bottom" width="24" nowrap="nowrap"> N
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 2.7pt;" valign="bottom" width="24" nowrap="nowrap"> N
</td> <td style="width: 16.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 2.7pt;" valign="bottom" width="27" nowrap="nowrap"> L
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 2.7pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> </tr> <tr style="height: 4.3pt;"> <td style="width: 35.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 4.3pt;" valign="bottom" width="59" nowrap="nowrap"> JANE
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 4.3pt;" valign="bottom" width="24" nowrap="nowrap"> E
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 4.3pt;" valign="bottom" width="24" nowrap="nowrap"> E
</td> <td style="width: 14.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 4.3pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 16.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 4.3pt;" valign="bottom" width="28" nowrap="nowrap"> L
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 4.3pt;" valign="bottom" width="24" nowrap="nowrap"> N
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 4.3pt;" valign="bottom" width="24" nowrap="nowrap"> N
</td> <td style="width: 16.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 4.3pt;" valign="bottom" width="27" nowrap="nowrap"> N
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 4.3pt;" valign="bottom" width="24" nowrap="nowrap"> N
</td> </tr> <tr style="height: 5.85pt;"> <td style="width: 35.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 5.85pt;" valign="bottom" width="59" nowrap="nowrap"> LIZZ
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 5.85pt;" valign="bottom" width="24" nowrap="nowrap"> E
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 5.85pt;" valign="bottom" width="24" nowrap="nowrap"> E
</td> <td style="width: 14.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 5.85pt;" valign="bottom" width="24" nowrap="nowrap"> E
</td> <td style="width: 16.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 5.85pt;" valign="bottom" width="28" nowrap="nowrap"> E
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 5.85pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 5.85pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 16.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 5.85pt;" valign="bottom" width="27" nowrap="nowrap"> N
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 5.85pt;" valign="bottom" width="24" nowrap="nowrap"> N
</td> </tr> <tr style="height: 1.85pt;"> <td style="width: 35.6pt; border-width: medium 1pt 1pt; border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; padding: 0cm 3.5pt; height: 1.85pt;" valign="bottom" width="59" nowrap="nowrap"> SANDY
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 1.85pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 1.85pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> <td style="width: 14.4pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 1.85pt;" valign="bottom" width="24" nowrap="nowrap"> E
</td> <td style="width: 16.55pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 1.85pt;" valign="bottom" width="28" nowrap="nowrap"> E
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 1.85pt;" valign="bottom" width="24" nowrap="nowrap"> E
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; padding: 0cm 3.5pt; height: 1.85pt;" valign="bottom" width="24" nowrap="nowrap"> E
</td> <td style="width: 16.45pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 1.85pt;" valign="bottom" width="27" nowrap="nowrap"> L
</td> <td style="width: 14.1pt; border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% yellow; padding: 0cm 3.5pt; height: 1.85pt;" valign="bottom" width="24" nowrap="nowrap"> L
</td> </tr> </tbody></table>

Sheet 2 I need to create a list with each shift so I can have the shifts and work groups sorted out.
I´m pretty sure that if I can get the first one to work I can get the rest.
In my head it sounds like this: if(sheet1!,B3:B11=”M”)list,A2:A4

thanx for the help
dz

Derek Brown · Nov 9, 2010

Could you please advise which version of Excel is being used, please.

dzoomz · Nov 10, 2010

I have 2007 home

Derek Brown · Nov 10, 2010

OK, that's good.
I created a workbook with the Excel 2007 solution last night, so I will produce a step-by-step guide when I get home tonight.
Later, I will create the formulas that will also work with earlier versions of Excel.

dzoomz · Nov 10, 2010

Woo

Thanks a lot for your time. I really appreciate it.
Good morning

Will this work even when I change the names on the schedule??

Derek Brown · Nov 10, 2010

Will this work even when I change the names on the schedule??

Yes, the process (I usually refer to it as "Filter across worksheets") will take whatever is in Column A and show it on other worksheets according the the required values (in your case it will be M, E, N and L).
I will also include a link to the solution to another post that appeared on the Message Board about the same time as yours did. Similar request to yours, but that had only one criteria instead of the four you need - and, for that solution, several columns of data were filtered to other worksheets.
The Excel 2007 step-by-step solution should be posted in just under 12 hours time.

Derek Brown · Nov 10, 2010

First of all the link to the other post: http://www.mrexcel.com/forum/showthread.php?p=2507031#post2507031
That solution is different to the one below but with similar formulas and, as you can see from the latest post, I will be adding to it.
Here are te formulas and instructions to give you a solution for Excel 2007/2010 that have the "IFERROR" function. Once you have confirmed that you have got it working the way you want, I will then provide the more lengthy formulas that will work for any version of Excel.
At first glance this may look complicated, but it is not - it is just that there is a lot of descriptive text. I recommend that you Copy/Paste the formulas to avoid typing errors.
First of all, an assumption: that cell A1 is blank (as in the example that you provided). If you do add something there later, it will be necessary to adjust formulas.
I will refer to your data sheet by the name "Master Sheet" - you will see that this is enclosed in single quotes in formulas because of the space in the name.
No changes are required to the Master Sheet - the formulas are required on the other sheets.
Step 1. Create a worksheet named "Control Sheet".
The name is not significant - most of my workbooks have such a sheet with a meaningful name. Here we will place a small table.
Cell A1: Type the name "Abbreviation".
Cell B1: Type the name "Day".
In each of Cells A2 to A8, enter the abbreviations that you have used in Row 1 on the Master Sheet. e.g SU, M, T, W, TH, F, S
In each of Cells B2 to B8 enter the full names for the days in Column A (e.g. Sunday, Monday etc.).
Select cells A2:B8 and give the range a 'name'. Do this by entering the name "WeekdayList" (without the quotes - note that there can be no spaces) and then making sure that you press 'Enter' (very important).
Step 2. Create a new worksheet and give it the name "1".
Step 3. Enter the following formula in cell E1:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)*1
The "*1" is required to ensure that the result is a number and not text. You will now see that the cell shows the name of the worksheet ("1" in this case).If you prefer to have a different name for the worksheet, that is OK but you will then have to enter the appropriate day number in cell E1.
Step 4. Enter the following formula in cell A1:
=COUNTA('Master Sheet'!A:A)+1
If you later enter something in A1 on the MASTER Sheet, then you will need to remove the "+1" from this formula. This shows the last row number in the MASTER Sheet and will change as you add and remove names.
Step 5. Enter the following formula in cell F1:
=VLOOKUP(INDEX('Master Sheet'!$B$1:$AF$1,1,MATCH($E$1,'Master Sheet'!$B$2:$AF$2,0)),WeekdayList,2,FALSE)
This will give you the day of the week to match the day number in cell E1.
Step 6. Enter the following values in the cells indicated:
A2: M
B2: E
C2: N
D2: L
E2: Morning
F2: Evening
G2: Night
H2: Leave
Step 6. Enter the following formula in cell A3:
=IFERROR(MATCH(A$2,OFFSET('Master Sheet'!$A$1,2,MATCH($E$1,'Master Sheet'!$B$2:$AF$2,0),COUNTA('Master Sheet'!$A:$A)-1,1),0)+2,"")
Now copy that formula across to cells B3, C3 and D3
This formula will show the row in which the first instance of the value in row 2 will be found - in the example that you gave it would show that the first instance of "M" for Day 1 will be found on Row 3.
Step 7. Enter a slightly different formula in cell A4:
=IFERROR(MATCH(A$2,OFFSET('Master Sheet'!$A$1,A3,MATCH($E$1,'Master Sheet'!$B$2:$AF$2,0),COUNTA('Master Sheet'!$A:$A)-1,1),0)+A3,"")
Copy this formula across to cells B4, C4 and D4.
ALSO now copy-down the formulas in A4:D4 as far as you need - this needs to be far enough to be able to show all the names in the MASTER Sheet. You can go beyond this if you want to but, one very important point, is that if you later want to print this sheet make sure that the Print Area includes the data only and not the rows that have formulas but no data.
Cells A4:D4 and downwards will show the row number for the "next" required name for the value in row 2.
Step 8. Enter the following formula in E3:
=IFERROR(INDEX(OFFSET('Master Sheet'!$A$1,0,0,$A$1,1),A3,1),"")
Copy this formula across as far as H3 and then down as far as you went with the formulas in Step 7.
Step 9. When you have checked and tested the worksheet, you can hide columns A, B, C and D.
Step 10. To create other worksheets, copy the one that has just been created (hold down the Ctrl key, left mouse-click the tab and drag it to the right). For example you will now get a sheet with the name "1 (2)" - rename the worksheet to "2" and that will now show the data for the second day. It is as simple as that - copy worksheet, change the worksheet name.
Finally. Let me know if I have missed anything or anything is not clearly explained. When it is working OK, I will give you the formulas for all versions of Excel.

I need to filter a schedule

dzoomz

New Member

Derek Brown

Well-known Member

dzoomz

New Member

dzoomz

New Member

Derek Brown

Well-known Member

dzoomz

New Member

Derek Brown

Well-known Member

dzoomz

New Member

Derek Brown

Well-known Member

Derek Brown

Well-known Member

Similar threads

Share this page

I need to filter a schedule

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock