1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Conditional format

Discussion in 'Business Applications' started by brain_hurtz, Jan 6, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. brain_hurtz

    brain_hurtz Thread Starter

    Joined:
    Jan 6, 2010
    Messages:
    5
    Not sure how to do this one.

    We have a spreadsheet at work to track unique part numbers. These parts eventually wear out but can be reworked and reinstalled again. 3 reworks is the cut-off meaning once installed the 3rd time I'd like it flagged to warn the operator. I'm not sure if countif or conditional formatting or both should be used.

    I've attached the file. The "Number" column has the part numbers we are tracking.
    Any help would be greatly appreciated !!
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi,
    I took a fast look at the sheet you attached.
    I don't know if I got it right.
    I see that Number 707803-1 occurs 3 times, once in Press 11 section A and once in section B and the third in Press 12 section A
    Would this mean that you want the part in Press 12 section A color let's say RED meaning that it's the third time it's replaced?

    The occureence has to be checked accross all the sheets (Press 11 through Press 33)?
    Does the number of sheets vary?
    Sheet's name will always be Press NN where NN is numeric?

    Maybe some additonal info?
    I think you could do it with some simple vba code

    Let me know.
     
  3. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Gelukkig Nieuwjaar Keebellah

    Quote
    I see that Number 707803-1 occurs 3 times, once in Press 11 section A and once in section B and the third in Press 12 section A
    Would this mean that you want the part in Press 12 section A color let's say RED meaning that it's the third time it's replaced?
    End Quote

    I would add

    Can and is a part used on more than one press?
     
  4. brain_hurtz

    brain_hurtz Thread Starter

    Joined:
    Jan 6, 2010
    Messages:
    5
    Exactly!! I put in 707803-1 in the spreadsheet 3 times as an example. What I need is for it to turn red the 3rd time it is installed. The number of sheets will not vary, press 11 thru press 33 will stay the same. The sheets names will stay the same.
     
  5. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    However, is the part used only on one press?
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi Brain,
    Like Villain asked, is the same part used on different presses and do you want to include the count of three accross the 22 presses or just per sheet?
    :confused:
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Gelukkig Nieuwjaar ook voor jou.

    Groetjes,
    Hans
     
  8. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Hans
    LOL Ik ben een Englesman maar ik heb in Amsterdam voor 13 jaar gewoont en spreekt een beetje Nederlands.
    Ik heb gezien dat je in het Netherlands woon't. Mijn dochter woont momenteel in Hilversum.
    Groetjes
    Les
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi Les,

    Je schrijft je Nederlandfs zoals een Engelsprkend persoon het spreekt, het is geen eenvoudige taal. Nederlands is ook "a terrible language".
    A mexican once described it to me: "Dutch isn't a language it's a sickness of the throat"

    Greetz,
    Hans
     
  10. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Inderdaad :)
     
  11. brain_hurtz

    brain_hurtz Thread Starter

    Joined:
    Jan 6, 2010
    Messages:
    5
    Yes I'd like the count to include the count of 3 across all presses on any worksheet. So 707803-1 could have appeared weeks or months later on any of the worksheets in the "Numbers" column.

    Appreciate you guys looking at this.
     
  12. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    I can do it in 2007 by selecting A4 to N50 in the Press11 worksheet then selecting the Home Ribbon and clicking on Conditional formatting, Highlight Cells Rules,Text that contains
    and then typing *-3 into the white text box and clicking on OK

    Ok I dont have Brains version of excel, but this is a clue to how you might do it.
    If there is somebody that Has Brains Exel version (it isn't 2007) and knows what to do in Conditional formatting try the following

    Opne the attachment that Brains has included in his first post.
    Click on the Press11 worksheet

    Select A4 to N50

    Select Format,conditional formatting

    This is where it gets grey. Can you see what options you have by clicking on the drop down icon to the right of "Formula Is" and see if there is any option for finding text.

    If so are you able to find soemthing that says Cell values contain. If so can you select that and type "*-3" wheer the asterisk is a wild card. Hoping that Excel will search for any item in the cells selected and only formatcells tat have -3 at the end

    If not you may have to try soemthing like

    In the white box to the right of "Formula Is" type the following
    =SEARCH("*-3",A4)

    Then click on Format... and set your format up

    Then click on Ok until you are back into your spreadsheet

    Hope that helps to give ideas of how it might be done.

    If you find the solution, taht can then be applied to all the other worksheets
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi there,

    I did some simple coding and put it in the sheet.
    Each sheet is triggered by a change
    The module checks all the sheets and colours red the third time the value is added with a dialog.

    All you'll have to do is see if works for you and maybe edit some but I think this is the idea?

    Just fill in a number in the columns A, F or K and see it
     

    Attached Files:

  14. brain_hurtz

    brain_hurtz Thread Starter

    Joined:
    Jan 6, 2010
    Messages:
    5
    Dear Keebellah,

    GREAT This is exactly what I need it to do! Another way I got it work on my own recently is with "countif" which I attached. Check out the sheet for press 22.

    But I think I like your version better. I do not know how to do vba code. If you have time can you code this for the 4th instance to code red instead of 3? The count of 3 was incorrect in my original design, it should alert them at 4. Is changing the dialog something simple that I can do? I would like it to say "Please mark with an X before installation."

    Thanks for the help, you're a lifesaver.

    Sincerely,

    brain_hurtz
     

    Attached Files:

  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi brain_hurtz,

    I'll get to it during the weekend en send you the instructions, onec you see it it's simple, really.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/891557

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice