Need Help generating text for cells based on restults of my formula

So basically I have a spreadsheet that tracks if a patient has turned in there required paperwork within the last 6 months and then changes the cell to white and lets me know how many days they have left until they are due to turn this paperwork in again. Then if it has expired I have a condition format change the cell to Red but I would like to add in the text EXPIRED to the cell. lastly I have Cells that have no data in them gray and I would like to add in red text saying No Paperwork.

I'm hoping this will be my last build of this spreadsheet so I can go ahead and start applying it to the real workbook
Thank you again ahead of time for the help everyone.

http://www.excelforum.com/excel-form...80#post3365280

I guess I should reference this topic as its where I got the working formula in the first place.

I don't believe you can combine icon with text when you are using conditional formatting. But I might be wrong.

You cant add text with Conditiona Formatting, but what you may want to try is to just have the text in the cell, but make it the same color as the cell if the condition is not met - then change teh font color

+ 1 what FDibbins said.

well if you take the forumula you already have there, a;; you need to do (I think) is add something like...

=IF(cell ref=condition-that-needs-to-be-met-to-show-text,"Your-Text",your-orginal-formula)

=IF(COUNT(E17:AB17)=0,"",(INDEX(E17:AB17,1,MATCH(99^99,E17:AB17,1))+180)-TODAY())

Is the current working formula, If I can even get it to just say expired when the value is = to a negative value that would be great

=IF(COUNT(E17:AB17)>=-1,"EXPIRED",(INDEX(E17:AB17,1,MATCH(99^99,E17:AB17,1))+180)-TODAY())
Works to display the word Expired but unfortunately when the value changes it doesn't display the value like it should. Did I miss up the code there or do I have to add something else?

Also I have no idea how to use the cell ref formula I'm still a baby to excel lol :P

maybe this?

=IF(COUNT(E17:AB17)>0,"EXPIRED",IF(COUNT(E17:AB17)=0,"",(INDEX(E17:AB17,1,MATCH(99^99,E17:AB17,1))+180)-TODAY()))

You changed your otiginal formula instead of adding another condition to it

umm duh, a count can never be less than 0 lol (my bad for not picking that up before)

what else would we use to ID when that text comes up?

edit: what about putting...
=IF(COUNT(E17:AB17)=0,"EXPIRED",(INDEX(E17:AB17,1,MATCH(99^99,E17:AB17,1))+180)-TODAY())

Doesn't work, that displays EXPIRED when there is no data input rather then when the value is a negative indicating that paperwork is past due.

I believe maybe we need to nest an if statement to validate the sum of the formula to return the correct result?

http://www.excelforum.com/excel-form...ment-help.html

Checking with the code section people since I feel this belongs there now.

Well please dont post another thread on this in the VBA section, it will get closed for duplicate thread

I was just thinking. you cannot have a negative date, nor a negative count, so maybe we need to build a MIN() in there somehow and compare that with the date you want to test for?

Can you give me an example of a row that you would want to show EXPIRED in?