hexagon logo
Register Sign in
  • Replies 7 replies
  • Subscribers 22 subscribers
  • Views 201 views
  • Users 0 members are here
Options
Related

Excel CONDITIONAL FORMAT editing

Matthew D. Hoedeman
Maybe I'm the last to know, but it has been a pain in my rump for quite some time...

EDITING CONDITIONAL FORMATS in Excel.

Need to change something in a cond-format, so you open it, click in the formula bar, but miss by 1 or more spaces, so you hit the arrow key to get to where you need to be but it screws up the entire formula by picking some other cell (in the direction of the arrow you hit). Sucks, right?

Well, simply hit F2 key and the arrows are now arrow movements within the formula. Have to hit F2 each time you click in a new formula bar.
Parents
  • Maybe I'm the last to know, but it has been a pain in my rump for quite some time...

    EDITING CONDITIONAL FORMATS in Excel.

    Need to change something in a cond-format, so you open it, click in the formula bar, but miss by 1 or more spaces, so you hit the arrow key to get to where you need to be but it screws up the entire formula by picking some other cell (in the direction of the arrow you hit). Sucks, right?

    Well, simply hit F2 key and the arrows are now arrow movements within the formula. Have to hit F2 each time you click in a new formula bar.

    Even after knowing about the F2 trick for some time now... I still manage to screw it up. Imagine wanting to go to the beginning of the line in the cell and poof everything ends up a mile away at the beginning of the row instead.
Reply
  • Maybe I'm the last to know, but it has been a pain in my rump for quite some time...

    EDITING CONDITIONAL FORMATS in Excel.

    Need to change something in a cond-format, so you open it, click in the formula bar, but miss by 1 or more spaces, so you hit the arrow key to get to where you need to be but it screws up the entire formula by picking some other cell (in the direction of the arrow you hit). Sucks, right?

    Well, simply hit F2 key and the arrows are now arrow movements within the formula. Have to hit F2 each time you click in a new formula bar.

    Even after knowing about the F2 trick for some time now... I still manage to screw it up. Imagine wanting to go to the beginning of the line in the cell and poof everything ends up a mile away at the beginning of the row instead.
Children
  • in reply to Kp61dude!
    I don't need to imagine it, I've dealt with it. I have over 2,000 cells in an Excel file that I am adding an additional format possibility to the 3rd conditional format. Condition 1 looks at the value of a specific cell, if it is equal to 1, it puts a red border around the cell (OUT of tol). Condition 2 is for the range of the study, too much range, it highlights the cell dull-yellow. Condition #3 is for hiding the cell (no border, no background, white text). This has been for selecting only 1 type of feature to be displayed, but I need to add to it that if the feature is IN tolerance and the SHOW ONLY OOT is turned on, it will also be blank. Thanks to the F2 ability and my programmable keypad, it is a SINGLE KEY STROKE on the keypad to do all of that.
  • in reply to Kp61dude!
    you have no idea how complex it is. The master (blank) file that I use to make reports for everything we check is 67mb in size.

    It can:
    (1) Show a roadmap of all features (feature ID's)
    (2) Average results from up to a 30-pc study
    (3) Show any single part from the study
    (4) Show ranges from the study
    (5) Show the tolerance for each feature
    All in the same cell on the report, all you have to do is change the value of 1 cell for what you want displayed and it all changes.

    It will also ONLY show OOT features (single cell data entry)
    It will let you show only SPC, or SURFACE, or TRIM, or HOLES or SINGLE AXIS (flatness and the like) features with a single cell data entry.

    You can toggle on/off body axis deviation letters

    You can have it show deviations for hole sizes or actual hole sizes

    And, of course, all the COND-FORMAT that change background, border, and all that.

    And all that is just for the "pretty picture" page. There are also raw data pages as well as a LEGACY look-alike report page.
  • in reply to Kp61dude!
    Blows my mind how crazy good some of these spreadsheet can get!
  • in reply to Kp61dude!
    Have you ever used the "INDIRECT" command in Excel? Pretty sweet way to make one spreadsheet grab data from another open spreadsheet. But a royal pain to make as it is 'text' and not copy & paste ready for the most part.
  • in reply to Kp61dude!
    Yes, I use Google Sheets for my home budgeting and every sheet makes use of the "INDIRECT" function, handy. Another one that I've started using a bunch is "QUERY" function (though not sure if Excel has that built in, I'm sure they do) https://support.google.com/docs/answer/3093343?hl=en.

    For work I've broken away from QC and therefore don't really touch Excel anymore but have devised other methods of crunching mass amounts of data like databases and C++/C# applications to then be available over the company web for anyone and everyone interested in numbers. I still use the good ol' Legacy text standard CMM report for QC department.
© 2024 Hexagon AB and/or its subsidiaries. Privacy Policy | Cloud Services Agreement