#1
|
|||
|
|||
change values based on cell
Hi,
Please see the attached sheet. I have put my question in the comment. question in comment is how can I change this formula to do the following: 1) if the value is not anything except "Y","y", "n","N", or blank it should through a warning message. Is using list box is alternative. 2) I want this cell to actually change color if the value of the cell F4 is either Yes or Y. also i should become must to fill this cell in this case. anyideas how to acheive this? have a look at sheet should make it easy to understand. Regards Umesh Banga |
#2
|
||||
|
||||
Hi Umesh,
You should be able to use a formula like: =IF(UPPER(F4)="Y","Fill",IF(UPPER(F4)="N","NA", "ERROR")) For the shading, use conditional formatting. However, your prescription for this isn't the same as your prescription for the IF test. For the IF test you allow only Y/N, but for the conditional format you say "cell F4 is either Yes or Y". If 'Yes' and 'No' are allowed, you need to change the IF test to: =IF(OR(UPPER(F4)="Y",UPPER(F4)="YES"),"Fill",IF(OR (UPPER(F4)="N",UPPER(F4)="NO"),"NA", "ERROR"))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Change values in cells based on criteria | SaneMan | Excel Programming | 2 | 02-02-2012 07:58 AM |
How to highlight the row based on cell value? | Learner7 | Excel | 1 | 12-11-2011 02:28 AM |
Automatic, Dependent Cell Values | flackend | Excel | 2 | 08-26-2011 07:01 AM |
How to count multiple values in a single cell, except zero? | iuliandonici | Excel | 1 | 04-13-2011 09:45 PM |
Sum & difference between multiple values in a single cell | iuliandonici | Excel | 4 | 04-13-2011 09:27 PM |