danielewicz
New Member
Hello and thank you for reading this!
Basically, I am trying to update our current work excel schedule to make it a lot better than it is! Currently, to keep things simple, it works like this:
1. Dates down the left
2. Colleague names at the top going to the right
3. Project details in the middle
Simple enough. However, we have currently just one sheet that houses all this information for 16+ colleagues and more. The sheet contains (going further to the right of the sheet and off the screen) "leave" which shows who is off that day, "freelancers" a tab at the end showing which non-colleagues are in and more.
I think it's a bit untidy. I want to implement a few changes but will only list the ones that I need your genius help with:
1. I want to move the "leave" section to a seperate sheet (Sheet B for example) - easy. BUT I want to create a rule that shows me a message if someone is away on a date I click. Example:
I click on Cell F15 on Sheet A (the main sheet): 1st February, Adam's Colum. It's empty, but on the "Leave" sheet (Sheet B) under that date he is shown as having holiday. When I select F15 on sheet A, a message appears telling me that he is away that day. Basically, the two cells on seperate sheets are linked and warn me if I select a date that someone is away. Is this or something like this possible?
2. I want to be warned if I enter a freelancer's name into the same date for two colleagues (a clash). Example:
Sheet A: I select Cell F15 (1st February, Adam's column) and Cell G15 (Stacy's column). I write "Meeting Anita" under F15, then the same thing for G15 - a mesage appears telling me I have a "clash". Is this possible? I'm assuming so but I guess I'm confused as to how this rule/validation would work. I guess it should be for each strip, going left along the table...
!!!NOTE!!!: Sometimes, both cells CAN have the same information, as this isn't a clash, for instance if Adam and Stacy were both "reading" then this doesn't clash - basically, can I avoid clashes for things that aren't a clash?
3. A tick box? Basically, we have a colleague collumn not with a name, but called "Freelancers". The idea is just to make a list of names per box (using ALT+ENTER to make a continued list of names per cell) to see who is in that day. This makes things a little tricky if trying to avoid duplicates, because its not just one name we're putting into a cell - its three or four. So my idea is this: can you create a collumn that lets you tick what names to put into that cell, so that you can put multiples in it?
4. Any hints and tips for creating work schedules that you know - this is my first time making something like this and any "avoid this" or "do this" would be invaluable.
I'm sorry if this is really long or too complicated but I really appreciate any help.
Thanks so much!
Basically, I am trying to update our current work excel schedule to make it a lot better than it is! Currently, to keep things simple, it works like this:
1. Dates down the left
2. Colleague names at the top going to the right
3. Project details in the middle
Simple enough. However, we have currently just one sheet that houses all this information for 16+ colleagues and more. The sheet contains (going further to the right of the sheet and off the screen) "leave" which shows who is off that day, "freelancers" a tab at the end showing which non-colleagues are in and more.
I think it's a bit untidy. I want to implement a few changes but will only list the ones that I need your genius help with:
1. I want to move the "leave" section to a seperate sheet (Sheet B for example) - easy. BUT I want to create a rule that shows me a message if someone is away on a date I click. Example:
I click on Cell F15 on Sheet A (the main sheet): 1st February, Adam's Colum. It's empty, but on the "Leave" sheet (Sheet B) under that date he is shown as having holiday. When I select F15 on sheet A, a message appears telling me that he is away that day. Basically, the two cells on seperate sheets are linked and warn me if I select a date that someone is away. Is this or something like this possible?
2. I want to be warned if I enter a freelancer's name into the same date for two colleagues (a clash). Example:
Sheet A: I select Cell F15 (1st February, Adam's column) and Cell G15 (Stacy's column). I write "Meeting Anita" under F15, then the same thing for G15 - a mesage appears telling me I have a "clash". Is this possible? I'm assuming so but I guess I'm confused as to how this rule/validation would work. I guess it should be for each strip, going left along the table...
!!!NOTE!!!: Sometimes, both cells CAN have the same information, as this isn't a clash, for instance if Adam and Stacy were both "reading" then this doesn't clash - basically, can I avoid clashes for things that aren't a clash?
3. A tick box? Basically, we have a colleague collumn not with a name, but called "Freelancers". The idea is just to make a list of names per box (using ALT+ENTER to make a continued list of names per cell) to see who is in that day. This makes things a little tricky if trying to avoid duplicates, because its not just one name we're putting into a cell - its three or four. So my idea is this: can you create a collumn that lets you tick what names to put into that cell, so that you can put multiples in it?
4. Any hints and tips for creating work schedules that you know - this is my first time making something like this and any "avoid this" or "do this" would be invaluable.
I'm sorry if this is really long or too complicated but I really appreciate any help.
Thanks so much!
rg, glad you are here
