SOLVED

formula and formatting help

Brass Contributor

Hey everyone, so I need help with a formula.  In the sheet attached I have "INSTR QUAL STATUS" cell (F15) and I tried using a IF AND Function but couldn't get it to work for me.  what I'm trying to do is have it say Active or Expired but only if there is data entered in either B16-E16 cell.  if there is no data entered in any of those cells I want F16 to be blank.

 

Also you will see some conditional formatting in cell E16.  I want the conditional formatting to work only if there is data entered in it.  if its blank I want it to remain white.

 

thank you for any and all help.

12 Replies
best response confirmed by spalmer (Brass Contributor)
Solution

@spalmer 

Change the formula to

 

=IF(COUNTBLANK(Table1[@[INSTR. QUAL. TYPE / LEVEL:]:[INSTR. QUAL. EXP. DATE:]]),"",IF([@[INSTR. QUAL. EXP. DATE:]]<TODAY(),"Expired","Active"))

Thank you Hans! would you happen to know how to fix that conditional formatting issue im having as well?

@spalmer 

Select the cell.

On the Home tab of the ribbon, click Conditional Formatting > Manage Rules...

Click Edit Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=AND(E16<>"",E16<TODAY())

 

Click OK, then click OK again.

 

fe
you are awesome Hans! thank you so much really appreciate your help and time

Alright Hans, so i have another issue that doesnt seem to be working with the conditional formatting. your formula you gave me did work and i also needed another one that showed in yellow when the exp date was coming up in 140 days. i tried that formula you gave me but didnt work and i tried the current formula thats in there now and even when i delete the data it still shows up yellow. would you be able to help me out with this? i attached a new book

@spalmer 

It's probably a matter of placing the rules in the correct order.

ya I have tried flipping the order but still didn't work.

@spalmer 

Did you look at the version that I attached?

 

Could you attach a workbook with the non-working rules? The one you attached to your previous reply was the same as the first one...

i apologize here it is

@spalmer 

You used OR instead of IF in the formula for yellow. If you change OR to AND, it'll work as intended:

 

S0491.png

 

See the attached version.

I thought I tried it that way but I probably didn't. thank you Hans. Again I really appreciate it
1 best response

Accepted Solutions
best response confirmed by spalmer (Brass Contributor)
Solution

@spalmer 

Change the formula to

 

=IF(COUNTBLANK(Table1[@[INSTR. QUAL. TYPE / LEVEL:]:[INSTR. QUAL. EXP. DATE:]]),"",IF([@[INSTR. QUAL. EXP. DATE:]]<TODAY(),"Expired","Active"))

View solution in original post