Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-21-2012, 12:05 AM
ubns ubns is offline change values based on cell Windows 7 32bit change values based on cell Office 2010 32bit
Competent Performer
change values based on cell
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default 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
Attached Files
File Type: xlsx Checklist.xlsx (13.8 KB, 9 views)
Reply With Quote
  #2  
Old 05-21-2012, 06:28 PM
macropod's Avatar
macropod macropod is offline change values based on cell Windows 7 64bit change values based on cell Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


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
change values based on cell How to highlight the row based on cell value? Learner7 Excel 1 12-11-2011 02:28 AM
change values based on cell Automatic, Dependent Cell Values flackend Excel 2 08-26-2011 07:01 AM
change values based on cell How to count multiple values in a single cell, except zero? iuliandonici Excel 1 04-13-2011 09:45 PM
change values based on cell Sum & difference between multiple values in a single cell iuliandonici Excel 4 04-13-2011 09:27 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:34 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft