Oh god someone help me please. This is ridiculous.

[cgs]

Hello!

I have just been placed in charge of a big work project based on excel and it's really quite complicated (to me). Any help would be so amazingly appreciated!! (particularly if you can send a link with screenshots!)

CHALLENGE #1

Ok so there is a column (From AU5:AU306) in this spreadsheet with dates when people need to be tested on stuff by. Our test is every 12 months, and people do them individually whenever they get a chance so it's not always 12 months exactly before they are retested.

I need the cells to be different depending on:

- Red if the due date has expired,

- Yellow if the due date is within 4 weeks, and;

- Green anytime before that.

I tried conditional formatting based on TODAY etc, but it comes up that it can't do it with relative referencing(?)

CHALLENGE #2

In column AV, there is a list of 3 options (for example D87, Y65 and J98. They represent a level of competency). This particular spreadsheet is being populated by others, so I needed to make sure they didn't stuff it up and hence "validated" it so they can only choose one of these three options.

It needs to be configured so that D87 is green, Y65 is yellow and J98 is red.

CHALLENGE #3

In the last column, it needs to be used to show whether people are overall up to date or not. This is derived from a number of other cells in each rowwhich indicate yes/no type answers. If any one of these is a 'no', it willmake the final column cell a 'no' too (or if they are all a 'yes', then the last column would be 'yes' too). How could this work?

Any help would be greatly appreciated!

 

[dguillett]

Hello!



I have just been placed in charge of a big work project based on excel and it's really quite complicated (to me). Any help would be so amazingly appreciated!! (particularly if you can send a link with screenshots!)



CHALLENGE #1



Ok so there is a column (From AU5:AU306) in this spreadsheet with dates when people need to be tested on stuff by. Our test is every 12 months, and people do them individually whenever they get a chance so it's not always 12 months exactly before they are retested.



I need the cells to be different depending on:



- Red if the due date has expired,



- Yellow if the due date is within 4 weeks, and;



- Green anytime before that.



I tried conditional formatting based on TODAY etc, but it comes up that it can't do it with relative referencing(?)



CHALLENGE #2



In column AV, there is a list of 3 options (for example D87, Y65 and J98.They represent a level of competency). This particular spreadsheet is being populated by others, so I needed to make sure they didn't stuff it up andhence "validated" it so they can only choose one of these three options.



It needs to be configured so that D87 is green, Y65 is yellow and J98 is red.



CHALLENGE #3

This appears? to be "homework" and you do NOT show us what YOU have tried so that we may comment, if desired.