Hi,

My manager has asked me to add some columns to our planning spreadsheet that
will allow us to easily see if another department is taking too long doing
its work. I need to create a fairly complicated IF formula, can anyone help?

Ive added four columns to the sheet, these are as follows:

A1: Where now?
Using the validation function Ive created a drop down box in this column
which has four options €œUs checking€�, €œTo Art€�, €œTo production€�, €œBack from
production€�.

A2: With whom
This simply lists the name of the person within the production department
who is working on the document.

A3: Deadline
This column is formatted as a date and shows the deadline by which the piece
of work should be returned to us.

A4: Late?
This is the column I want to format. If A3 is empty I want this cell to be
blank. If the deadline in A3 has passed this cell should display the word
€œLATE€�. If the deadline has passed, BUT the drop down box in A1 is set to
€œBack from production€� then this cell should display the word €œDONE€�.

Can anyone tell me what this formula should be?

Thanks

Karl

Hi Karl
Try
=IF(A3="","",
IF(A3-TODAY()=0,"",
IF(AND(A3-TODAY()<0,A1="Back from Production"),"Done","Late")))

--
Regards

Roger Govier


"Karl" wrote in message
...
Hi,

My manager has asked me to add some columns to our planning
spreadsheet that
will allow us to easily see if another department is taking too long
doing
its work. I need to create a fairly complicated IF formula, can anyone
help?

I've added four columns to the sheet, these are as follows:

A1: Where now?
Using the validation function I've created a drop down box in this
column
which has four options "Us checking", "To Art", "To production", "Back
from
production".

A2: With whom
This simply lists the name of the person within the production
department
who is working on the document.

A3: Deadline
This column is formatted as a date and shows the deadline by which the
piece
of work should be returned to us.

A4: Late?
This is the column I want to format. If A3 is empty I want this cell
to be
blank. If the deadline in A3 has passed this cell should display the
word
"LATE". If the deadline has passed, BUT the drop down box in A1 is set
to
"Back from production" then this cell should display the word "DONE".

Can anyone tell me what this formula should be?

Thanks

Karl

Thank you.

It works, except that when you choose "Back from production" from the drop
down box in A1 "LATE" doesn't change to "DOE".

Are IF STATEMENTS just incapable of recognising the contents of validated
cells?

Thanks

Karl

"Roger Govier" wrote:

Hi Karl
Try
=IF(A3="","",
IF(A3-TODAY()=0,"",
IF(AND(A3-TODAY()<0,A1="Back from Production"),"Done","Late")))

--
Regards

Roger Govier


"Karl" wrote in message
...
Hi,

My manager has asked me to add some columns to our planning
spreadsheet that
will allow us to easily see if another department is taking too long
doing
its work. I need to create a fairly complicated IF formula, can anyone
help?

I've added four columns to the sheet, these are as follows:

A1: Where now?
Using the validation function I've created a drop down box in this
column
which has four options "Us checking", "To Art", "To production", "Back
from
production".

A2: With whom
This simply lists the name of the person within the production
department
who is working on the document.

A3: Deadline
This column is formatted as a date and shows the deadline by which the
piece
of work should be returned to us.

A4: Late?
This is the column I want to format. If A3 is empty I want this cell
to be
blank. If the deadline in A3 has passed this cell should display the
word
"LATE". If the deadline has passed, BUT the drop down box in A1 is set
to
"Back from production" then this cell should display the word "DONE".

Can anyone tell me what this formula should be?

Thanks

Karl

Hi Karl
Works fine for me.
Are your sure the wording is the same in the dropdown box as the formula
"Back from Production"

--
Regards

Roger Govier


"Karl" wrote in message
...
Thank you.

It works, except that when you choose "Back from production" from the
drop
down box in A1 "LATE" doesn't change to "DOE".

Are IF STATEMENTS just incapable of recognising the contents of
validated
cells?

Thanks

Karl

"Roger Govier" wrote:

Hi Karl
Try
=IF(A3="","",
IF(A3-TODAY()=0,"",
IF(AND(A3-TODAY()<0,A1="Back from Production"),"Done","Late")))

--
Regards

Roger Govier


"Karl" wrote in message
...
Hi,

My manager has asked me to add some columns to our planning
spreadsheet that
will allow us to easily see if another department is taking too
long
doing
its work. I need to create a fairly complicated IF formula, can
anyone
help?

I've added four columns to the sheet, these are as follows:

A1: Where now?
Using the validation function I've created a drop down box in this
column
which has four options "Us checking", "To Art", "To production",
"Back
from
production".

A2: With whom
This simply lists the name of the person within the production
department
who is working on the document.

A3: Deadline
This column is formatted as a date and shows the deadline by which
the
piece
of work should be returned to us.

A4: Late?
This is the column I want to format. If A3 is empty I want this
cell
to be
blank. If the deadline in A3 has passed this cell should display
the
word
"LATE". If the deadline has passed, BUT the drop down box in A1 is
set
to
"Back from production" then this cell should display the word
"DONE".

Can anyone tell me what this formula should be?

Thanks

Karl

You're right, it works fine.I'd left a space in where none should be.

Thanks again, very much appreciated!

"Roger Govier" wrote:

Hi Karl
Works fine for me.
Are your sure the wording is the same in the dropdown box as the formula
"Back from Production"

--
Regards

Roger Govier


"Karl" wrote in message
...
Thank you.

It works, except that when you choose "Back from production" from the
drop
down box in A1 "LATE" doesn't change to "DOE".

Are IF STATEMENTS just incapable of recognising the contents of
validated
cells?

Thanks

Karl

"Roger Govier" wrote:

Hi Karl
Try
=IF(A3="","",
IF(A3-TODAY()=0,"",
IF(AND(A3-TODAY()<0,A1="Back from Production"),"Done","Late")))

--
Regards

Roger Govier


"Karl" wrote in message
...
Hi,

My manager has asked me to add some columns to our planning
spreadsheet that
will allow us to easily see if another department is taking too
long
doing
its work. I need to create a fairly complicated IF formula, can
anyone
help?

I've added four columns to the sheet, these are as follows:

A1: Where now?
Using the validation function I've created a drop down box in this
column
which has four options "Us checking", "To Art", "To production",
"Back
from
production".

A2: With whom
This simply lists the name of the person within the production
department
who is working on the document.

A3: Deadline
This column is formatted as a date and shows the deadline by which
the
piece
of work should be returned to us.

A4: Late?
This is the column I want to format. If A3 is empty I want this
cell
to be
blank. If the deadline in A3 has passed this cell should display
the
word
"LATE". If the deadline has passed, BUT the drop down box in A1 is
set
to
"Back from production" then this cell should display the word
"DONE".

Can anyone tell me what this formula should be?

Thanks

Karl

The case must also be the same.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Karl
Works fine for me.
Are your sure the wording is the same in the dropdown box as the formula
"Back from Production"

--
Regards

Roger Govier


"Karl" wrote in message
...
Thank you.

It works, except that when you choose "Back from production" from the
drop
down box in A1 "LATE" doesn't change to "DOE".

Are IF STATEMENTS just incapable of recognising the contents of
validated
cells?

Thanks

Karl

"Roger Govier" wrote:

Hi Karl
Try
=IF(A3="","",
IF(A3-TODAY()=0,"",
IF(AND(A3-TODAY()<0,A1="Back from Production"),"Done","Late")))

--
Regards

Roger Govier


"Karl" wrote in message
...
Hi,

My manager has asked me to add some columns to our planning
spreadsheet that
will allow us to easily see if another department is taking too
long
doing
its work. I need to create a fairly complicated IF formula, can
anyone
help?

I've added four columns to the sheet, these are as follows:

A1: Where now?
Using the validation function I've created a drop down box in this
column
which has four options "Us checking", "To Art", "To production",
"Back
from
production".

A2: With whom
This simply lists the name of the person within the production
department
who is working on the document.

A3: Deadline
This column is formatted as a date and shows the deadline by which
the
piece
of work should be returned to us.

A4: Late?
This is the column I want to format. If A3 is empty I want this
cell
to be
blank. If the deadline in A3 has passed this cell should display
the
word
"LATE". If the deadline has passed, BUT the drop down box in A1 is
set
to
"Back from production" then this cell should display the word
"DONE".

Can anyone tell me what this formula should be?

Thanks

Karl

Not so, Bob.
Not using EXACT within the formula nor FIND.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
The case must also be the same.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Karl
Works fine for me.
Are your sure the wording is the same in the dropdown box as the
formula
"Back from Production"

--
Regards

Roger Govier


"Karl" wrote in message
...
Thank you.

It works, except that when you choose "Back from production" from
the
drop
down box in A1 "LATE" doesn't change to "DOE".

Are IF STATEMENTS just incapable of recognising the contents of
validated
cells?

Thanks

Karl

"Roger Govier" wrote:

Hi Karl
Try
=IF(A3="","",
IF(A3-TODAY()=0,"",
IF(AND(A3-TODAY()<0,A1="Back from Production"),"Done","Late")))

--
Regards

Roger Govier


"Karl" wrote in message
...
Hi,

My manager has asked me to add some columns to our planning
spreadsheet that
will allow us to easily see if another department is taking too
long
doing
its work. I need to create a fairly complicated IF formula, can
anyone
help?

I've added four columns to the sheet, these are as follows:

A1: Where now?
Using the validation function I've created a drop down box in
this
column
which has four options "Us checking", "To Art", "To production",
"Back
from
production".

A2: With whom
This simply lists the name of the person within the production
department
who is working on the document.

A3: Deadline
This column is formatted as a date and shows the deadline by
which
the
piece
of work should be returned to us.

A4: Late?
This is the column I want to format. If A3 is empty I want this
cell
to be
blank. If the deadline in A3 has passed this cell should display
the
word
"LATE". If the deadline has passed, BUT the drop down box in A1
is
set
to
"Back from production" then this cell should display the word
"DONE".

Can anyone tell me what this formula should be?

Thanks

Karl