LinkBack URL
About LinkBacks
Hi everybody,
I am in the process of creating for work a rostering/labour costing spreadsheet over different sheets.
Here’s how I want it to work:
- On the sheet named Roster I have 2 different inputs per day e.g.; Sun 29 March has Columns B & C. B will be normal Rostered hours which has a drop down menu gathered by Data Verification on Sheet 2 (Normal Time, Sick, Annual Leave, etc). In the subsequent row have rostered shift times. In Column C has the options for overtime. The idea behind the 2 columns is that it is possible to work a normal shift of 8 hours and overtime for 2.
- On Sheet Lb07.02.15 is a labour costing spreadsheet which is then uploaded to other software. Formatting must remain. On this sheet we input an Earning class which I have an IF formula looking at B3, D3, F3, etc for the phrase “Normal Time.” True result gives a code, False returns a blank cell.
The issue is I am stuck with the cells I10-I16 when it comes to inputting any shift that does not have an earnings class (BO, Annual Leave, Carers Leave, Long Service, Sick). Essentially I have gone with an IF/OR combination that looks at cell Roster!B3 for a value that matches the requirement, If it finds a match it will display the result in I10.
So If A. Person is sick on the Sunday, choosing sick on the roster page automatically updates I10 to say sick. Likewise for Annual Leave, LWOP, etc.
I have been getting the result I want if I narrow the formula to look for “sick” only, but as soon as I add the rest of the options it gets screwey. It mostly returns the first logical choice (sick) and not any other when selected.
The formula I have been trying looks like this:
=IF(OR(Roster!B3={"Annual Leave","Sick”,"LWOP","Long Service","Carers Leave"}),Roster!B3,””)
What am I missing?
Thanks in advance
Register To Reply
Bookmarks