UKC

Any Excel experts out there?

Does anyone know an easy formula to say if any of cells F13:28 have the value "No" then give value "Please provide justification", otherwise give blank cell value?

Thanks for any help!

Chris

In reply to Chris Sansum:
Lookup "if statement"

=if("logical test",true value,false value)

In reply to Chris Sansum:
=if(f13="No","Please provide justification"," ")


then copy it down

In reply to MaxWilliam:

Sorry, that's not quite what I'm after - I want to check a range of cells, and if any of them have 'No' in them give one statement 'Please provide justification'. I could do nested IF statements, but that seems a bit over complex...

In reply to MaxWilliam:

ie one statement on the whole sheet in response to any of the questions being answered with 'No'.

In reply to Chris Sansum:

Sorry mate, been looking at excel since 7am this morning...best I can come up with would be:

=IF(OR(A1="No", B1="No", C1="No", D1="No", E1="No"), "Please provide justification", "")

Obviously, you'll have to add the additional cells...but you get the idea. Far from elegant, I'm afraid

In reply to Chris Sansum:

Hidden column G containing 1 if F is NO, null otherwise
then if statement based on sum G13:28

Not very neat, but would work

In reply to ThunderCat:

Sounds good - I thought there might be an 'OR' involved! I'll give it a try.

In reply to Chris Sansum:

Can't you just have a hidden cell that has a countif function to look for 'No' then test if that cell has a value greater than 0 to display the provide justification text?

In reply to Chris Sansum:

=IF(COUNTIF(A1:A100,"no")>0,"Please provide justification","")

A1:A100 is any range of cells you want to look for a 'no' in

In reply to kingborris:



See? Much more elegant than mine.

In reply to kingborris:

Yep, even better!

In reply to MaxWilliam:

Thanks all. Knew someone would have the answer!

In reply to Chris Sansum:

that software is the devils work!

In reply to jazzyjackson:

I once got a load of horrible data that needed to be cleaned up. One of the jobs was to take the name column and make sure each name was capitalised (ie joe bloggs had to be Joe Bloggs)

Spent ages writing a bit of script that took the cell, chopped up the contents based on the position of spaces, took the first letter, capitalised it, made sure the rest of the word was lower case.

The found out there was a formula that did it for you called =Proper()

Every days a school day.