Hi
I am having problems with the formula
The results I'm looking for are:
Please Login or Register to view this content.
If any cells in the range F16:N16 are blank, return "Input Req'd"
If all cells contain data, return "Overdue" / "Today" / "Tomorrow" if matched, or "ok" if not.
F16:N16 are blank cells, with no formulae.
When I use the edit formula button to check, it shows the expected result, but When there are blank cells, the cell return is "ok"
Thanks for your time
Jon
Use Countblank(). This counts number of blanks in the range.
Also, not sure why you have all those ISNUMBER(MATCH(... functions...not needed here.
Please Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Perfect, thanks for your help!
You're welcome....
Here's an even better/shorter formula....
Please Login or Register to view this content.
Thanks again, works a treat.Originally Posted by NBVC
You're welcome....
Here's an even better/shorter formula....
Please Login or Register to view this content.
Would you mind explaining how this works, as I have other formulae which I may be able to simplify using a similar solution?
COUNTBLANK(F16:N16)>0...this part counts blanks within the specified range and if there is more than 0, then the result is TRUE and so "Input Req'd" is returned.
If there are no blanks, then the second part of the formula kicks in... IF(E16=0,"",IF(OR(O16={"Overdue","Today","Tomorrow"}),O16,"ok"))
This part first checks what is in E16 and if there is a 0, it returns a blank and ends. If it not a 0, then the formula checks to see if O16 contains either of those 3 words. If either word is found, it simply returns what is in O16 (which would be the respective word found). If it doesn't find either word, then "ok" is returned.
This part: OR(O16={"Overdue","Today","Tomorrow"}) is just a simplified way of writing the more familiar.... OR(O16="Overdue",O16="Today",O16="Tomorrow") ... no need to repeat the O16 reference.
Hope that helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks