Anyone help me with an Excel spreadsheet

[dUnKle]

I am trying to design a spreadsheet on exel for work

Its meant to be a sort of seating plan, a list of who sits where and when (along with times of breaks and who covers them etc)

The sheet is various times across the top and then in the columns below the names of the staff

Now I impressed myself by working out how to have the cells bring up a drop down list of the names but I want to be able (if possible) to do the following

(1) If a name is chosen in the column then it can not be selected again in that column (for example person a is chosen in top cell of column 1 they can not be picked from the drop down list in that same column)

(2) A colour is assigned to different sets of names. We have 5 teams, each represented by a colour. Is it possible when choosing a name from the drop down list the cell is coloured to represent that persons team

 

[dUnKle]

anyone ?

 

[Deleted member 30535]

Excel 2003 or 2007?

 

[Codehead]

You can probably do the colouring using conditional formatting. Not sure how you'd link the person to a team without resorting to macros and vlookup though.

As for the first bit, I've just had a rummage around the validation dialog in Excel 2003 and there's no facility for stopping duplication that I can see. You could achieve it through forms and macros, but I think that's going a bit far.

[EDIT]

Will this do the trick?

http://www.cpearson.com/excel/NoDupEntry.aspx

 

[dUnKle]

Excel 2003 or 2007?

God
Good question
Not at wok at mo but belive its 2003

Not that fussed if the duplication thing can not be done but would really like to be able to do the colour thing

For example if Person A is on blue team then when he is chosen the cell is automatically coloured blue

 

[Deleted member 30535]

Conditional formatting would work for the colours... but in 2003 you can only have 3 conditional rules.

 

[dUnKle]

You can probably do the colouring using conditional formatting. Not sure how you'd link the person to a team without resorting to macros and vlookup though.

As for the first bit, I've just had a rummage around the validation dialog in Excel 2003 and there's no facility for stopping duplication that I can see. You could achieve it through forms and macros, but I think that's going a bit far.

[EDIT]

Will this do the trick?

Untitled Page

seems to be what I want
cheers

 

[dUnKle]

Conditional formatting would work for the colours... but in 2003 you can only have 3 conditional rules.

And I assume you would have to have 1 condition for each person ?
Bugger

 

[Codehead]

Looks like conditional formatting using COUNTIF will do the trick

I got the colour coding working using =COUNTIF($J$8:$J$9,E8) where J8:J9 was the team list and E8 was the cell to be checked, you can only add 3 conditions with Excel 2003 though.

I've uploaded an example of the conditional formatting, (no duplicate handling though)

[edit] Mr Incredible and I seem to be having a hivemind moment.

It's three conditions per cell, so you'd be limited to three teams.

 

[Deleted member 30535]

And I assume you would have to have 1 condition for each person ?
Bugger

See Codeheads example. You assign up to three conditions PER CELL, and based upon three criteria you can "colour" (or put lines around) a cell depending on a value of that cell or another cell. In 2003 you are limited to three colour-criteria per cell.

 

[Henry17]

Why are you still using 2003 as new version is quite good and working great too. Well if you still got problems then let me know i will tell you about it but make sure its office 2007.

 

[liveforav]

because he's using a work pc, so it's unlikely he'll have much control over the version they use. Most places still use 2003, some are even on 97!