Logical

[Sherees]

This is what I want to know..
cell B1 & C1 is the cell to enter the duties of two staffs (namely X & Y).
B1 is for staff X, C1 is for staff Y. staff can chose any one of duty in B1
& C1 frm a dropdownlist of 10 different duties(like "Break shift", "10 - 6
PM", "10 - 2 PM"..and so on)..

We have another cell D1 E1 & F1 where we enter the staff strength in the
shop on three intervals of time (say 10 - 2PM, 2 - 6PM, & 6 - 10PM)
Assume D1 cell to show the staffs present in the shop during time interval
10 - 2PM, E1 for 2 - 6PM & F1 for 6 - 10PM.

Now for eg, staff X chose her duty as Break Shift in cell B1(assume cell B1
as Jan 1, B2 as Jan 2 and so on), now we enter in D1 & F1 digit 1. & the
other staff chose 2 -10pm duty in C1 now the staff strenght cells are as
follows...

D1 shows 1, E1 shows 1 &F1 shows 2... This is entered manually now checking
the staff duty.. Is it possible to automate it.. When the staff's select
their duty the staff strength column in the particular day must show up the
number of staffs as per the duty automatically.. Pls help me

rgds,

 

[Kaman]

When you say staff X or Y, are you referring to one employee or a group of
employees? How many employees is D1/E1/F1 trying to add up?

 

[Kaman]

I don’t understand what “break shift†means or what the other 10 choices
might be for B1/C1 as it relates to D1 (10a-2p), E1 (2p-6p) and F1 (6p-10p).
I am assuming 3 of the 10 choices are going to be either (10a-2p), (2p-6p),
or (6p-10p) and the other 7 are irrelevant as far as your question is
concerned. I am also assuming that there is only one entry per day (row) in
either B1, C1, or both. You could put a fancy formula in D1 with nested IF
or COUNTIF statements, but I always like to go with simple, even if it’s a
roundabout way of doing it. Since I’m assuming just 2 employees and 3
relevant outcomes, here is what I would do….

Insert 6 columns after C1 (which will be hidden later), moving your three
shop strength totals over to J1/K1/L1. The first 3 inserted columns
(D1/E1/F1) would be for Staff X/10a-6p, Staff X/2p-6p, and Staff X/6p-10p,
respectively. And G1/H1/I1 would be for Staff Y/10a-6p, Staff Y/2p-6p, and
Staff Y/6p-10p. In cell D1 you would put: =IF (B1=â€10a-2pâ€,1,0). In cell
E1: =IF (B1=â€2p-6pâ€,1,0); and so on.

Then in your shop strength columns over in J1/K1/L1 you would simply add the
relevant cells. In cell J1 you would put: =D1+G1. In cell K1: =E1+H1. In
cell L1: =F1+I1. Then hide columns D-I and protect those cells!

If my assumptions are off, then this route may not be feasible.

 

[Sherees]

Kaman

Thanks a lot, u said it simple, i was making it complicated...thank u

 

[Sherees]

Hi kaman,
wat is the formula if i want to say for eg: if B1="break shift" or
"10a-2p/w.off" or "10a-2p/p.off" or 10a-6p the column 10a-2p must show 1,
otherwise 0..

U said if(b1="10a-2p",1,0), wat is the formula if i want to say if
b1="10a-2p",1 or 10a-6p,1 more than one conditions for 1

I hope that u got me, please come back to me

 

[p45cal]

You may not have seen an answer to this here
http://www.thecodecage.com/forumz/excel-miscellaneous/161926-how-programme-excel.html