how to account for a 100 reasons

smiler44

Registered User.
Local time
Today, 06:35
Joined
Jul 15, 2008
Messages
678
I am creating a spread sheet to show who is at work and who is off during the week. Not only may people be at work or off all day, they may also be at work or off part day. I have to account for 20 people, for 6 days and for about 100 different reasons. A formula would be very long and time consuming, this I could do if excel will allow me 100 reasons.
I need to fill in a cell with their start time, a cell with their finish time and a 3rd cell with what they are doing. sick, holiday, training are 3 examples of why someone may not be at work. each reason for not being at work is represented by a 2 letter code. the code could be in one of two cells for each person. my finished sheet is sheet1, the source of information is sheet2. all data on sheet 2 gets deleted each time the user does what they want and then pastes new date in next time. its always pasted in the same place and same format
what is the best way of doing this, formula, vlookup other?
I cant do vlook up so if this is the way to go I will need to learn this so pointers to training would be appreciated.

I have attached a spread sheet for a Monday to give some idea, hope it is of help.

thank you smiler44
 

Attachments

Thank you Rookie. I have had a look, would I have to use a drop down box each time I want to get the correct response? I was looking to automate it as 20 people x 6 days would be 120 drop down boxes.

smiler44
 
You did not provide a full spec, so I provided a link to get started. I am sure there must be other approaches, but I am not an Excel Specialist, so I cannot recommend any. Perhaps someone else here in the Forum has additional ideas.
 
sorry Rookie, always difficult to explain things. I of course understand perfectly cos I wrote the post:)

this is a formula I have already in place but it only caters for a handful of reasons for someone not being at work. I need to cater for about 100 and was wondering if there is a better way then adding some 90 other if's

=IF(AND(Dpam!C3="R",Dpam!D3="OR"),"RDO",IF(Dpam!D3="LL", "LL",IF(Dpam!D3="OR","work",IF(Dpam!C3="R","RDO",IF(Dpam!D3="BH","BH",IF(Dpam!D3="BH LL","BH LL", IF(Dpam!D3="","work",IF(Dpam!D3="LA","LA work",IF(Dpam!D3="LP","work LP")))))))))


smiler44
 
Hi smiler44,

To remove the limitation of 7 nested functions in Excel formulas, you might consider VBA instead.

Then you can have as many as you like.

VBA is going to be a big step if you're learning VLOOKUPs but I suggest you look into some training & have a view to include VBA.

To get you going though, I can write a macro to do this if you supply all the conditions as I've outlined below, such as "You want Cell 'D6' on Sheet1 to display 'sick' if the value of Cell 'D6' on Sheet2 = 'sk' for example.

Using VBA, you could have an If ... Then ... Else or probably better, a Select case statement.

A Select case would go someting like (This is only a very general explanation by the way);

Case When Sheet2(B6="sk")
Then Sheet1(D6="sick")
End

You then add an event such as if someone adds a value to Sheet2, the macro runs & updates Sheet1.

It's worth considering VBA study as part of your role if you're going to be doing this sort of work for any prolonged length of time.
 
I don't really understand what is going on but I would never do my "conversion" of 100 codes within the formula, I would use a table and use Vlookup , Vlookup is easy to use all you need to do is read the help , it really is simple.

Brian

Ps why is the sk in a different column to the other codes?
 
Either VBA either the lookup function can do the trick.
I prefer VBA because is more flexible.

But I write this post only because I'm wonder if you use somehow the information about reasons.
Do you perform some statistical computation about reasons to not be at work ? (counts, percentages etc)
If not why all this troubles ? Allow any employe to type the reason using hes / hers own words. Maybe by specifying a maximum numbers of words (2,3,4 ...)
 
Hi Brian,

I would have suggested VLOOKUP but there is > 1 condition in the formula.

Such as here - =IF(AND(Dpam!C3="R",Dpam!D3="OR"),"RDO" ...

I don't see how VLOOKUP can cope with that but then you've educated me before with your knowledge! :)
 
I agree, I said that I didn't understand what was happening and that was part of it, I was just stating that I would not have a formula for changing so many values.

A Select Case as you suggested is better than multiple If but it could still be a maintenance nightmare. I suspect a flawed approach as I think does Mihail.

Brian
 
Agree, Brian.
Maybe I'm wrong to say that
Either VBA either the lookup function can do the trick.
On the other hand I'm sure that VBA can solve this in a elegant manner.

As I say, I don't post to offer a solution because if you are here is enough to find the right one.
Only I ask if all this stuff is necessary. And I don't expect an answer to this question. It is only for the first poster think.
 
Thank you Rookie. I have had a look, would I have to use a drop down box each time I want to get the correct response? I was looking to automate it as 20 people x 6 days would be 120 drop down boxes.

smiler44

Just realised what you asked here, a drop down is for values in a column, so I think that you would only need one

Brian
 
Once you create a dropdown box for one cell it can be repeated in every other cell. You can limit the cell contents to the options in the dropdown, or the user can be given the opportunity to add their own - I don't think you want the latter in this case.

DaiDropDown.xls has a q&d example for you ....

You use Data Validation to achieve the dropdown ...
 

Attachments

Users who are viewing this thread

Back
Top Bottom