Excel help

[marine1]

I need some help please guys, I am by no means an excel pro.

I have a workbook:

First page has a shift roster with drop down days, such as Monday day, Monday night. from the third page, where the list written down in different shaded colours.
I added a new one, Saturday night but now it is not available in the drop down box on the fist page. Help.
How do I see the hidden formulas or text or whatever they are using?

If I manually try enter Saturday night in 1st page it wont allow me to. I do have the password for the file.

 

[*TJR*]

I assume it is excel 2007 and I further assume the drop down list range was defined in name manager. Go to the formulas ribbon, then to name manager and see if the list is displayed there. If so, look at the name range and update the range to include the new days.

 

[vinodh]

Quite often they hide columns that have calculations. Select all the columns, right click on one of them and the select "unhide". It should then make all columns visible.

 

[marine1]

If I unhide I get this on the right hand side:
=IF(B7="",0,VLOOKUP(B7, Shifts, 4,FALSE))

 

[marine1]

I assume it is excel 2007 and I further assume the drop down list range was defined in name manager. Go to the formulas ribbon, then to name manager and see if the list is displayed there. If so, look at the name range and update the range to include the new days.

You are a god damn genius !!!!! thank you
So now it is there but not linked to the formula

 

[marine1]

If I "show calculation steps" I see where the issue is, it is leaving the new "sat night " line out but I cannot edit it, not sure where to do that?

 

[marine1]

Update ok I fixed it, dont know how but now the colour is not showing up in the first page even though I added it to conditional formatting

 

[*TJR*]

If you can wait for tomorrow, e-mail me the file to [email protected].

I will look at it and send it back.

 

[Lounger]

Can you copy the worksheet and upload it to www.filegooi.co.za. (remove names) Then it's easier to advise/learn.

 

[marine1]

Thanks boys will do both now.
Appreciate your help.

 

[marine1]

here

 

[Lounger]

Um ...
According to the notes:

2) If you change the shift description or colors, you must manually update the affected shift assignments in the Schedule worksheet

Could that mean you just need to copy the yellow Saturday Night format (copy/paste special/paste format) from Shifts to Schedule?

 

[marine1]

but then how come the others work without doing that

 

[Lounger]

Not sure. I take it this is one of the standard MS templates - or based on http://office.microsoft.com/en-us/templates/9-to-5-work-schedule-TC101863404.aspx

 

[marine1]

Not sure. I take it this is one of the standard MS templates - or based on http://office.microsoft.com/en-us/templates/9-to-5-work-schedule-TC101863404.aspx

lol looks exactly the same

 

[marine1]

You deleted something about proofing??? I can't find it on the sheet

 

[*TJR*]

OK, this is the easy and lazy option (seeing it is Xmas). Highlight all the cells where the dropdown menus are. Change the colour of these cells to "yellow" with 2 shades, Yellow and White (or as you wish the format should be for Saturday Night). Now for all other menu choices it will be as per the conditioning format. For Saturday Night it will be yellow/white.

The file is here: http://filegooi.co.za/get2/901c29488f4c70c230f6127a60772002/WeeklyShiftScheduleTemplate2.xlsx

Sorry I could't find a quick solution.

 

[marine1]

Thanks can you maybe explain what I did wrong? If I now want to add more colours with different information? Is it something to do with conditional formatting?

 

[*TJR*]

What different information? Maye I can give it a go.

 

[marine1]

I need another selection to say 1) off 2)missing 3) sick 4) leave 5)double shift

Thanks so much