The Elsmar Cove Wiki More Free Files The Elsmar Cove Forums Discussion Thread Index Post Attachments Listing Failure Modes Services and Solutions to Problems Elsmar cove Forums Main Page Elsmar Cove Home Page
Go Back   The Elsmar Cove Forum > Common Quality Assurance Processes and Tools > Quality Tools, Improvement and Analysis > Quality Assurance and Compliance Software Tools / Solutions > Excel .xls Spreadsheet Templates and Tools
Forum Username


Search the Elsmar Cove
Custom Search
Monitor the Elsmar Forum
Follow Marc & Elsmar
Elsmar Cove Forum RSS Feed  Marc Smith's Google+ Page  Marc Smith's Linked In Page   Marc Smith's Elsmar Cove YouTube Page  Marc Smith's Facebook Page  Elsmar Cove Twitter Feed
Elsmar Cove Groups
Elsmar Cove Google+ Group  Elsmar Cove LinkedIn Group  Elsmar Cove Facebook Group
Sponsor Links





Donate and $ Contributor Forum Access

Click the graphic above.
Courtesy Quick Links

Links that Elsmar Cove visitors will find useful in your quest for knowledge:

Howard's
International Quality Services
Marcelo Antunes'
SQR Consulting
Bob Doering's
Correct SPC - Precision Machining

NIST's Engineering Statistics Handbook
IRCA - International Register of Certified Auditors
SAE - Society of Automotive Engineers
Quality Digest Portal
IEST - Institute of Environmental Sciences and Technology
ASQ - American Society for Quality
Reply
 
Thread Tools Search this Thread Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
  #1  
Old 1st May 2006, 03:22 PM
Jennifer Kirley's Avatar
Jennifer Kirley Jennifer Kirley is offline
Forum Moderator

 
Registration Date: Jan 2004
Location: Maine, USA
 
Posts: 5,454
Thanks Given to Others: 3,305
Thanked 3,098 Times in 1,770 Posts
Karma Power: 645
Karma: 28563
Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.
Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.
Please Help! Formatting an Excel .xls spreadsheet to alarm out-of-calibration status

Suppose a program has spreadsheet worksheets with their lists of gages, their cal due dates and other calibration-relevant data and the supervisors are responsible to know when their worksheet's gages are coming due. The practice of browsing a list of gages to find an out-of-calibration date could be improved upon, I think.

For example, I have seen cells that turn red under certain parameters (some kind of logic function) and I think it's cool but I haven't figured out how to do that. Can anyone tell me how it's done?

I have had trouble with logic functions using dates. Other than Julian date (which would work) can a cell be so formatted to show a message "Danger Will Robinson!" (okay, I'm kidding about the message) based on a past-due date?

Thanks in advance!

__________________

"If you only have a hammer, you tend to see every problem as a nail." Abraham Maslow

Sponsored Links
  #2  
Old 1st May 2006, 03:28 PM
Al Rosen's Avatar
Al Rosen Al Rosen is offline
Super Moderator

 
Registration Date: Jun 2002
Location: Lawn Guyland
Age: 64
 
Posts: 3,549
Thanks Given to Others: 71
Thanked 746 Times in 457 Posts
Karma Power: 418
Karma: 7230
Al Rosen is appreciated, and has over 1700 Karma points.Al Rosen is appreciated, and has over 1700 Karma points.
Al Rosen is appreciated, and has over 1700 Karma points.Al Rosen is appreciated, and has over 1700 Karma points.Al Rosen is appreciated, and has over 1700 Karma points.Al Rosen is appreciated, and has over 1700 Karma points.Al Rosen is appreciated, and has over 1700 Karma points.Al Rosen is appreciated, and has over 1700 Karma points.Al Rosen is appreciated, and has over 1700 Karma points.Al Rosen is appreciated, and has over 1700 Karma points.Al Rosen is appreciated, and has over 1700 Karma points.
Send a message via AIM to Al Rosen
Quote:
In Reply to Parent Post by Jennifer Kirley

Suppose a program has spreadsheet worksheets with their lists of gages, their cal due dates and other calibration-relevant data and the supervisors are responsible to know when their worksheet's gages are coming due. The practice of browsing a list of gages to find an out-of-calibration date could be improved upon, I think.

For example, I have seen cells that turn red under certain parameters (some kind of logic function) and I think it's cool but I haven't figured out how to do that. Can anyone tell me how it's done?

I have had trouble with logic functions using dates. Other than Julian date (which would work) can a cell be so formatted to show a message "Danger Will Robinson!" (okay, I'm kidding about the message) based on a past-due date?

Thanks in advance!
Look up "conditional formatting" in Excel Help. It will explain it

__________________

Al
Sponsored Links

  #3  
Old 1st May 2006, 03:54 PM
Tim Folkerts's Avatar
Tim Folkerts Tim Folkerts is offline
Super Moderator

 
Registration Date: Sep 2003
Location: Kansas, USA
Age: 51
 
Posts: 975
Thanks Given to Others: 29
Thanked 365 Times in 218 Posts
Karma Power: 153
Karma: 5874
Tim Folkerts is appreciated, and has over 1700 Karma points.
Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.Tim Folkerts is appreciated, and has over 1700 Karma points.
Jennifer,

Use the "Conditional Formatting" option on the "Format" Menu.

The simplest method is to compare the cell value to a specific number. For instance, you could apply a format when ever a number is less than zero, or when it is between -3 and +3.

The next option is to campare to another cell. For example, you could calculate the upper control limit and then compare a series of cells to that cell.

The final option (I I just recently discovered) is to create a formula as a condition. When the conditional formatting box is open, look at the left most drop-down menu and choose "Formula Is". For example, if you select a range (starting at cell B4) and type the formula

=B4<TODAY()-30

it will test if something is 30 days past due. If you add a second condition like

=B4<TODAY()+7

it can test if it is coming due in the next week.

After you set the condition, you can have the cell color change by selecting "Format", the "Borders". Perhaps a month over due is red, slightly overdue is orange, and due in the next week is yellow.


I could attach a simple example if it would help...

Tim F

__________________

To wonder is to begin to understand.
  #4  
Old 1st May 2006, 04:00 PM
Jennifer Kirley's Avatar
Jennifer Kirley Jennifer Kirley is offline
Forum Moderator

 
Registration Date: Jan 2004
Location: Maine, USA
 
Posts: 5,454
Thanks Given to Others: 3,305
Thanked 3,098 Times in 1,770 Posts
Karma Power: 645
Karma: 28563
Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.
Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.
Quote:
In Reply to Parent Post by Tim Folkerts

I could attach a simple example if it would help...Tim F
Let me fool with it a bit and see what I come up with.

Thanks guys!

Always learning...

__________________

"If you only have a hammer, you tend to see every problem as a nail." Abraham Maslow
  #5  
Old 1st May 2006, 04:28 PM
Jennifer Kirley's Avatar
Jennifer Kirley Jennifer Kirley is offline
Forum Moderator

 
Registration Date: Jan 2004
Location: Maine, USA
 
Posts: 5,454
Thanks Given to Others: 3,305
Thanked 3,098 Times in 1,770 Posts
Karma Power: 645
Karma: 28563
Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.
Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.
The color formatting (I used red for 1st formatting: past due, and gold for 2nd formatting: dates within a week of due date) didn't work when the referenced date was more than a month away from any of the listed due dates.

__________________

"If you only have a hammer, you tend to see every problem as a nail." Abraham Maslow
  #6  
Old 1st May 2006, 04:38 PM
tomvehoski tomvehoski is offline
Courtesy Access

 
Registration Date: May 2001
Location: Warren, MI
 
Posts: 944
Thanks Given to Others: 0
Thanked 277 Times in 169 Posts
Karma Power: 155
Karma: 3995
tomvehoski is appreciated, and has over 1700 Karma points.
tomvehoski is appreciated, and has over 1700 Karma points.tomvehoski is appreciated, and has over 1700 Karma points.tomvehoski is appreciated, and has over 1700 Karma points.tomvehoski is appreciated, and has over 1700 Karma points.tomvehoski is appreciated, and has over 1700 Karma points.tomvehoski is appreciated, and has over 1700 Karma points.tomvehoski is appreciated, and has over 1700 Karma points.tomvehoski is appreciated, and has over 1700 Karma points.
Try this:

Condition 1:

Cell Value Is Less Than =TODAY() Format is Red

Condition 2:

Cell Value Is Less Than =TODAY()+7 Format Gold

Once a condition is met, Excel stops evaluating further conditions. So even though a past due gage is also less than today + 7, Excel never checks since condition 1 was met. Make sure you enter the = sign.
  #7  
Old 1st May 2006, 04:59 PM
Jennifer Kirley's Avatar
Jennifer Kirley Jennifer Kirley is offline
Forum Moderator

 
Registration Date: Jan 2004
Location: Maine, USA
 
Posts: 5,454
Thanks Given to Others: 3,305
Thanked 3,098 Times in 1,770 Posts
Karma Power: 645
Karma: 28563
Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.
Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.Jennifer Kirley is appreciated, and has over 1700 Karma points.
Thanks Tom! I will try that this evening I think.

__________________

"If you only have a hammer, you tend to see every problem as a nail." Abraham Maslow
  #8  
Old 2nd May 2006, 12:10 PM
Dave Dunn's Avatar
Dave Dunn Dave Dunn is offline
Involved - Posts

 
Registration Date: Jul 2003
Location: River Falls, Wisconsin
Age: 45
 
Posts: 131
Thanks Given to Others: 1
Thanked 31 Times in 24 Posts
Karma Power: 59
Karma: 757
Dave Dunn is appreciated, and has over 700 Karma points.Dave Dunn is appreciated, and has over 700 Karma points.Dave Dunn is appreciated, and has over 700 Karma points.Dave Dunn is appreciated, and has over 700 Karma points.Dave Dunn is appreciated, and has over 700 Karma points.Dave Dunn is appreciated, and has over 700 Karma points.Dave Dunn is appreciated, and has over 700 Karma points.
If the gages are listed in a long worksheet and have the calibration due date listed, you could sort the worksheet using the due date. That way you not only could find the ones that are overdue, but you could keep track of the ones that are coming due.
Reply

Lower Navigation Bar
Go Back   The Elsmar Cove Forum > Common Quality Assurance Processes and Tools > Quality Tools, Improvement and Analysis > Quality Assurance and Compliance Software Tools / Solutions > Excel .xls Spreadsheet Templates and Tools

Do you find this discussion thread helpful and informational?


Bookmarks


Visitors Currently Viewing this Thread: 1 (0 Registered Visitors (Members) and 1 Unregistered Guest Visitors)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Forum Search
Display Modes Rate Thread Content
Rate Thread Content:

Forum Posting Settings
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Discussion Threads
Discussion Thread Title Thread Starter Forum Replies Last Post or Poll Vote
Supplier Rating Excel .xls Spreadsheet form sxbalasu Supplier Quality Assurance and other Supplier Issues 41 24th January 2013 11:20 AM
Excel Spreadsheet Conditional Formatting help needed samsung Excel .xls Spreadsheet Templates and Tools 12 25th November 2010 03:52 AM
5 Whys Template - Excel .xls Spreadsheet jrubio ISO/TS 16949 - International Automotive Quality Systems Standard 10 27th October 2009 03:15 PM
Within Part Variation - Excel .xls calculation spreadsheet shahshahani Excel .xls Spreadsheet Templates and Tools 11 29th June 2005 01:36 PM
Seeking: Simple Excel .xls SPC spreadsheet jager Excel .xls Spreadsheet Templates and Tools 15 30th October 2003 03:38 PM



The time now is 03:07 AM. All times are GMT -4.
Your time zone can be changed in your UserCP --> Options.


   


Marc Timothy Smith - Elsmar.com
8466 LeSourdsville-West Chester Road, Olde West Chester, Ohio 45069-1929
513 341-6272