Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations MintJulep on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Formatting Spill-over 1

Status
Not open for further replies.
MToomey

MToomey

Mechanical
Joined
Dec 2, 2019
Messages
17
Location
US
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
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
Not necessarily any simpler, but you could create a template row with formatting turned off, and copy/insert row for the added drawing.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529 Entire Forum list
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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!).
 
Upvote 0 Downvote
I believe the button or quick key you're looking for is "clear formatting."
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

StrucCyclist
  • Locked
  • Question Question
Calculation Presentation/Formatting
Replies
13
Views
2K
IFRs
IFRs
BhagyeshVashi
  • Locked
  • Question Question
Need a rename macro for excel
Replies
11
Views
2K
sdz
sdz
Kenja824
  • Locked
  • Question Question
excel Formula Find help
Replies
5
Views
1K
Kenja824
Kenja824
jhn1611
  • Locked
  • Question Question
Compass question excel
2 3 4
Replies
62
Views
3K
IDS
IDS
3DDave
  • Locked
  • Question Question
Python now integrated into Excel - apparently
Replies
8
Views
2K
IDS
IDS

Part and Inventory Search

Sponsor

Back
Top