IF or Vlookup

[safir4]

Hi All,

Im new to this forum, and was wandering if you can help me with below spreadhseet query.

I have field called Date of Inspection, cell D1. (21/12/2012)

I have a field call Report Due, cell C2.

I want to create a formula that looks at the D1, and then the current date Today(). If the inspection report has not been sent within a 15 day period the cell will become highlighted in red, and say report due. And if the report has been received the cell will remain highlighted in yellow.

I want to create an alert that tell's me if the report has not been received.


I hope you can help.........

[Kevin UK]

Hi safir4

Do you mean like this!

In C2: =IF(TODAY()-D1>15,"Report Due","")

And conditional format, new rule: =TODAY()-$D$1>15

[safir4]

Hi Kevin,

Thank you very much for your help this has helped me greatly.

If C2 Displays "Report Due", how do i make the field turn red everytime the formula returns that answer.

Once last question.

Just say I have 4 fields called (Report 1, Report 2, Report 3, Report)

So when ever I receive one of the 4 reports I will fill in the cell and say either yes or No.

I want to create a formula in E1, which looks at all 4 fields and if they say yes then the say "All reports received". But if we are awaiting for one of the reports to be received and cell say's No, then E1 should say "Awaiting Reports".

Your help would be greatly appreciated.

Thank you

[safir4]

Hi Kevin,

Thank you very much for your help this has helped me greatly.

If C2 Displays "Report Due", how do i make the field turn red everytime the formula returns that answer.

Once last question.

Just say I have 4 fields called (Report 1, Report 2, Report 3, Report)

So when ever I receive one of the 4 reports I will fill in the cell and say either yes or No.

I want to create a formula in E1, which looks at all 4 fields and if they say yes then the say "All reports received". But if we are awaiting for one of the reports to be received and cell say's No, then E1 should say "Awaiting Reports".

Your help would be greatly appreciated.

Thank you

[BB1972]

safir,

Assuming your "fields" are A1, B1, C1 & D1, in E1 enter: =IF(AND(A1="Yes",B1="Yes",C1="Yes",D1="Yes"),"All reports received","Awaiting reports")

[martindwilson]

=if(countif(b1:d1,"yes")=4,"all reports recieved","awaiting reports")
or if(countif(b1:d1,"no"),"awaiting reports","all reports recieved")

[safir4]

Martin I've tried above formula and its not working, im typing it up as below:


=if(countif(b1:d1,"yes")=4,"all reports recieved","awaiting reports") or if(countif(b1:d1,"no"),"awaiting reports","all reports recieved")

Please tell me if I am inserting it correctly, if not can you please advise.

[BB1972]

They are two separate formulas:

Formula 1
=if(countif(b1:d1,"yes")=4,"all reports recieved","awaiting reports")

Formula 2
=if(countif(b1:d1,"no"),"awaiting reports","all reports recieved")

[safir4]

How do i convert the 2 formulas into one.

What i mean is:

IF B1:D1 = Yes, then "All reports received", but if B1:D1 is No then "Awaiting reports".

Please Note all 4 cell's have to say yes, in-order for the answer from the formula to be "All reports received"

[djapigo]

safir,

They are logically the same formula... just use the first one...

[safir4]

I've tried both the above fomulas, and they don't work, isn't giving the correct answer.

[BB1972]

Did you try the formula I provided you with in post # 5?

[safir4]

My mistake i've got the formul to work.
d
Thank you all for your time and help. Much appreciate

[djapigo]

safir,

If BB's formula also does not work, I suggest that you attach a sample spreadsheet with the formula that you are using... any of the formulas should work above...

To attach a spreadsheet, click on Go Advanced, then seach for a paperclip...

[safir4]

Thank you Djapigo. In the future ill attach the spreadsheet.

A question (In post 2 I was given a formula, how do i make the cell colour change "Red" for example when the answer is "Report Due".

[BB1972]

safir,

Click on cell E1, then on the Home tab in the Ribbon, click on Conditional Formatting, New Rule, Use a formula to determine which cells to format, then type:

Please Login or Register  to view this content.

Click on Format, Fill, select your colour, then Ok, then Ok, then Apply.

[djapigo]

Hi safir,

Kevin's suggestion is to use Conditional Formatting, which is under the Home tab under Styles...

Highlight your range that you want special formatting, press the Conditional Formatting, New Rule, Use a formula to determine which cells to format...

Then use Kevin's formula in the box, press Format... and select your "Red" formatting...

Let me know if you are still stuck...

re-post: Sorry Brendan, nice work...

[safir4]

Test.xlsx

Guys i've attached the spreasheet that I am working on with post. As you will it applies red to all the cell's, rathern then only highligting the cell's that say "Reports Due" red.

What I am looking for is if the cell's say:

* Reports Due (Then the cell should highlight it self red)
* Not Due (Then the cell should highlight it seld yellow)

Your help will be greatly appreciated.

[djapigo]

Hi safir,

Use Brendan's formula...
=F5="Reports Due" with the red fill
=F5="Not Due" with the yellow fill

Here's a screen shot of how your conditional formatting should look like in the end...



Let us know if this is not what you want...

Dennis

[safir4]

Thank you. Will do that

[Kevin UK]

Hi safir4

Late to the party, but i see you have it sorted. Attached is your file with the formatting applied. i have also applied data validation to cells G5:J12, so just click the down arrows for yes or No.

[safir4]

I've had to re-open my post as I need so further help.

If you look at the attached spreadhsheet, when their is no date inserted within cell C8, the formula in DE returns the answer as (#VALUE!), What do i need to add into the formula that say's if their is no valuve within C8 the result in D8 should say blank.

Your help would be greatly appreciated.

Test(1).xlsx

[BB1972]

safir,

You can do a couple of things. Either wrapping your entire formula in D8 with IFERROR like this:

Please Login or Register  to view this content.

will return a blank ("") if your formula evaluates/results in an error, which is what happens when you ask Excel "is today, minus (blank) greater than 15".

You can also just leave the cells in Column C blank (rather than typing in "(Blank)", as you have done) - if you do that, as I have done in C10, you will see the D10 says "Reports Due", rather than evaluating to an error.