This content is likely not relevant anymore. Try searching or browse recent questions.
SHEETS IF Function - change text in cell depending on the data in two other cells

Hi I am First time poster,

I am stumped! I have attached the spreadsheet and the formula I am struggling with is on the "Master Job List" sheet and is in Cell B. This is the spreadsheet! https://drive.google.com/file/d/1RFuA-PneZQg6WO5irYhfnQAIMFHgSh-c/view?usp=sharing

I need to have a cell (B) change to one of four options "WAITING FOR BRIEF", "NEW", "OVERDUE","IN PRODUCTION" the cell will change depending on the data in two other cells. I am completely unsure if this is even possible, I have problem-solved multiple formulas but am coming up at a loss so any help and suggestions would be welcomed.

This is what I need to happen.

If there is no text in Cell (A) the text in Cell (B) needs to say "WAITING FOR BRIEF"
If there is a date in Cell (A) and is within seven days of today the text in Cell (B) needs to say "NEW"
THEN... The formula needs to be overridden
If there is a date in Cell (U) and it is before today but after the date in Cell (T) the text in Cell (B) needs to say "IN PRODUCTION"
If the date in Cell (U) is after today the text in Cell (B) needs to say "OVERDUE"

This is the current formula I have which does not tick the seven days option and is coming up as an error

=AND(IF(ISBLANK(A7),"WAITING FOR BRIEF","NEW"),IF(OR(U7<TODAY(),"OVERDUE","IN PRODUCTION")))

I have tested formulas in Cell B7 in the "Master Job List" Sheet - Any suggestions?
Details
Pinned
Locked
All Replies (3)

Hi,
This is my interpretation of your posted requirements. Can you check to see if it is interpreted correctly and specify what the intent is for the unknown cases?

```IF                                 Then B= A = ""                             Waiting for Brief U > today                          Overdue U < today and U > T                In production U < today and U <= T               unknown U = today                          unknown U = "" and (today - A) <= 7        New U = "" and (today - A) > 7         unknown```

Note that` A = "" `is the same as A is blank.

Ed

G...

THIS IS AMAZING! Thank you so much. I am not familiar with Len formulas so I am going to teach myself this function now.

RE the complete - this is a manual adjustment that the producers need to do when they have finished. This is purposefully manual. But thank you so much for checking.
This question is locked and replying has been disabled.
10 characters required
You will lose what you have written so far.
Personal information found

We found the following personal information in your message:

This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?

Report abuse
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Report post
What type of post are you reporting?
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

This reply is no longer available.
Search
Clear search
Close search