Help with formula

[rwe1187]

Let's say I have cells E5 - E10 and F5 - F10. I want a message to appear in cell F11 that basically says "if the value in any cell E5-E10 is <> 8 and E5 - E10 are blank then display message 'check cells' but of E5 - E10 = 8 then cells F5 - F10 can be blank but if the values in cells E5 - E10 are <> 8 and there is any text in F5 - F10 then cell F11 is blank"... I can get the formula to work in individual cells H5 - H10 and I can get it to work in F11 for just one cell in the noted range, but I cannot get it to work for all cell ranges and display in one cell.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: I know this will be easy for most....
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[rwe1187]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: I know this will be easy for most....
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I had no clue that two websites were run by the same company or moderator. I posted there and got a response that I did not care for so I left. I did a search for EXCEL FORMULA HELP and got two websites, so therefore I assumed they were two totally different entities. I assumed cross-posting was posting the same question in two different areas of the same site. Not trying to be smart here, just explaining my thinking process.

 

[Fluff]

so therefore I assumed they were two totally different entities.

They are two completely different websites.

I assumed cross-posting was posting the same question in two different areas of the same site.

Nope, that is duplicate posting. Cross posting is posting the same question on multiple sites.
Did you read the link at the end of the rule as it explains about cross posting. Excelguru Help Site - A message to forum cross posters

 

[Fluff]

Is this what you wanted

=IF(SUM((E5:E10<>8)*(F5:F10=""))>1,"C")

 

[rwe1187]

No. I will keep trying. This will be an "IF, AND" or "IF,AND,OR" not a SUM. I have the formula, just cannot force it to display in only one cell while checking a range. I tried the : and that just gives an error. If E6<>8 and F6 is blank then display a message, but if E6=8 and F6 is blank then display nothing or if E6 is blank and F6 is blank then display nothing. Then the same thing for E7/F7, E8/F8 and so on....but I want the error to display in one cell only, F11...maybe that is how I should have worded it.

 

[Fluff]

What is E6<>8 and F6 is blank, but both E7=8 & F7 is blank, what should be displayed?

 

[rwe1187]

If E6 is greater than or less than 8, then there should be a reason in F6 entered manually. If there is no reason in F6, then an error message needs to be displayed.
If E6 equals 8 then there does not need to be anything in F6, so that can be blank and no error message needs to be displayed.
If E6 is blank and F6 is blank, then no error needs to be displayed.

Repeat above for E7 and F7. Repeat for E8 and F8. Repeat for E9 and F9. Repeat for E10 and F10.

Display the error message in F11 for all that are above. The contents of the error message can be anything.

 

[Fluff]

Yes but if you want one cell with an error, what should happen if some of the rows are ok & some are not?

 

[Fluff]

If you want an error message if any row is wrong then try

+Fluff 1.xlsm
	E	F
5	7
6	8
7	9	text
8
9
10
11		Check
Main

Cell Formulas
Range	Formula
F11	F11	=IF(SUM((E5:E10<>"")*(E5:E10<>8)*(F5:F10=""))>=1,"Check","ok")