×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Formatting Spill-over

thread770-499706

Excel Formatting Spill-over

Excel Formatting Spill-over

(OP)
Hi folks,

I'm working on a large-scale revision of a special project based on customer/structural comments.
I have a spreadsheet to track what tasks are completed. First column is drawing number, subsequent columns are comments on each particular drawing. This leaves one task per cell, organized by drawing number.
As each task when is completed, I will fill the cell with a highlight color, and highlight the drawing number when that drawing is complete and ready for review.
Due to the nature of the commenting process, I will get comments on a drawing that require changes to another drawing that wasn't on the list. As I work through the comments and discover a drawing that needs to be added, I will insert a row where that drawing belongs alpha-numerically and add the relevant info.

Here's my issue: when I add a new row, excel decides that I want the same cells filled as above or below. I want the cells to not be filled because they are not complete yet. So I select all the highlighted cells in the new row and fill them with no color. Then I type the drawing number and comments in the now blank cells, and as soon as I hit tab, it re-fills them with the color.
Sure, I can just manually un-fill them each time, but it's getting aggravating. Surely there must be a way to change this behavior.

EDIT: I'm running the Office 16 version of excel if that changes any answers.

Any thoughts would be appreciated.
Matt Toomey

RE: Excel Formatting Spill-over

I don't know how to stop the autoformat for new rows, but what I'd do here is add a status column, fill that in with words like "in progress", "complete" or whatever, and use that to drive some conditional formatting to highlight cells however you want. Add some data validation dropdowns to the status column if you want, although that's probably more trouble than it's worth here. The conditional formatting should get copied to inserted rows, but that's fine since the data stored in the status column won't, so nothing will get highlighted until you fill that cell in. This has the added advantage of not using formatting to store data (data being completion status here), which I don't like since formatting is too easy to accidentally change. You can also filter the list by completion status, and maybe even get really fancy and make a pivot chart to show percent complete or whatever.

RE: Excel Formatting Spill-over

You are inserting a row into a region - Excel will do just what you say it does.

If you add data to a blank row at the end and then sort the row will be placed as desired with the default formatting.

Other than that - you can insert the row, then copy/paste a blank row and to overwrite the formatting.

I would tend to structure this more like a database - a separate sheet with the drawing number, the comments, a date or other flag about completion and then the display sheet uses references and conditional formatting to determine the coloring.

RE: Excel Formatting Spill-over

(OP)
Thanks folks,

Barring any new revelations, I think that just about covers it. Excel is being excel, and there is no simple way to change that.
Stick - I do appreciate your point about formatting storing data. I hadn't considered it that way before, but I see exactly what you mean (especially in light of the question that brought up that answer!).

RE: Excel Formatting Spill-over

I believe the button or quick key you're looking for is "clear formatting."

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login

Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now
Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close