Can this be done???? Please help!!

[sparky3883]

Hi all.

I have a wee rota that i have created for work and was wondering if th
following 'problem/query' is possible.

My rota is set up with 8 coulmns: Employee Name is one, the other
columns are headed Sunday to Monday.

I have 30 staff - not all staff work the same shifts or same days eac
week.

What i am wanting to do, is,using the Data| Filter | Advance Filte
command, filter all staff who are working on the monday from th
'Weekly Rota' (sheet 1 ) to a daily rota (sheet 2). At the moment
have been doing it by selecting the Monday column, selecting 'No
Blanks' on the filter command, and then filtering the Column over t
Sheet 2. This all works fine, but I am trying to find out whether it i
possible to filter all the shifts for a certain day, ALONG with th
staff names of the people who are only working that day. This i
proving to be somewhat difficult and i have been looking at doing thi
for a few weeks now and am not having much luck.

Can someone please put me out of my misery and tell me if this i
possible?

Any help would be very very much appreciated

 

[Ed]

If you have a routine that is working for you now, you might try recording
that into a macro. The recording will give you specific column, row, and/or
cell references. You can then edit the code to use InputBox to create text
strings to use as filter criteria for the day and shift.

HTH
Ed

 

[Arvi Laanemets]

Hi

Let's do it by example - you can adjust it to your needs yourself.

We have a sheet Weekly, with table
Employee, Monday, Tuesday, ..., Sunday

Add an additional column Selected to left of this table (column A)

We have a sheet Day, with combo for day selection in cell B1 (I explain
later how to create combo in cell), and a table starting from row2
Employee, Hours
(I assume here, that in Weekly table for every day are entered working
hours)

Define named ranges (Insert.Name.Define)
DailyDay=Dayly!$B$1
Days=Weekly!$C$1:$I$1
DynRange=OFFSET(Weekly!$B$2,0,MATCH(DailyDay,Days,0),ROW(Weekly!$A4)-ROW(Wee
kly!$A$1),1)
Selected=OFFSET(Weekly!$B4,0,MATCH(DailyDay,Days,0))
SelectedRng=OFFSET(Weekly!$A$2,,,COUNTIF(Weekly!$B:$B,"<>")-1,1)

The names for defined ranges can be diferent from those in my example - but
then you have to make adjustments in formulas (and name definitions) which
contain them)

On Weekly sheet, enter into cell A2 (Selected column) the formula
=IF(Selected>0,COUNTIF(DynRange,">0"),"")
and copy it down at least for such amount of rows as you have employees (but
you can have some amount of rows ready for new employees).

On Daily sheet, into cell A3 (column Employee) enter the formula
=IF(ISERROR(MATCH(ROW($A3)-ROW($A$2),Book1!SelectedRng,0)),"",OFFSET(Weekly!
$B$1,MATCH(ROW($A3)-ROW($A$2),SelectedRng,0),0))

On same sheet, into cell B3 (column Hours) enter the formula
=IF(ISERROR(MATCH(ROW($A3)-ROW($A$2),Book1!SelectedRng,0)),"",OFFSET(Weekly!
$B$1,MATCH(ROW($A3)-ROW($A$2),SelectedRng,0),MATCH($B$1,Days,0)))

Copy both formulas down for same number of rows as the formula on sheet
Weekly

It's almost done! Select the weekday - oops! Select the cell B1 on sheet
Dayly, and then Data.Validation.List from menu. Into list source enter the
formula
=Days
and press OK
Now select the weekday, and employees being at work on this day and their
working hours are displayed.