I have a spread sheet where i want to create a fomula where when the operator enters a number it will turn green if it is within the specification limits and red if it is out of specfication limits. Is there a formula that will do that.
I have a spread sheet where i want to create a fomula where when the operator enters a number it will turn green if it is within the specification limits and red if it is out of specfication limits. Is there a formula that will do that.
=a1> your specification limits
=a1< your specification limits
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Using Conditional Formatting:
highlight outside tolerance: =AND(A1<>"",OR(A1<$D$1,A1>$D$2))
highlight inside tolerance: =AND(A1<>"",A1>=$D$1,A1<=$D$2)
where cell D1 contains the minimum value and D2 contains the maximum value.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I have attached an example of my spreadsheet. What I want to be able to do is when the operator enters there reading it will either turn green for good or red for bad. I do not want it to read true/false.
Great workbook ... unfortunately, I have no idea where the operator inputs figures nor what the minimum and maximum values are.
Have you tried to apply the example I provided ... it doesn't go in a cell; it goes in a Conditional Formatting formula.
Regards, TMS
The green highlighted area is where the operator will put their readings and the blue highlighted area are the min. specs. No max. The only way I know how to set up formulas are in cells.
See the updated example.
I have changed the header layout to separate the minimum values and applied Conditional Formatting. I have only done it on the left side. You will need to make the same adjustments and apply similar Conditional Formatting on the right hand side. Should be a good learning exercise
Regards, TMS
Thanks, I did figure it out and used it on 7 other spread sheets. I have attached one spread sheet where I was not able to get it to work and I can not figure out why.
I might be missing something but the attached workbook looks very much like the original with no Conditional Formatting applied.
I regret that I have no plans to start again from scratch.
Regards, TMS
sorry, attached wrong file
The CF is only set up on the left hand side (as per post #7).
What is it, other than that, that does not work.
Regards, TMS
The throat always shows red. Can not get it to show green just in those sections.
The minimum value cells are formatted as text.
Reformat them as general then press F2 and Enter to recommit each cell as a numeric value.
Regards, TMS
You're welcome. Thanks for the rep.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks