• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pls Help- and sorry if jargon is bad. I have no excel training.

marymary

New Member
I would like to use formulas and / or conditional formatting in my spread sheet to accomplish the following:
Doc is being used like a project plan where column A has tasks and sub tasks.

#1 I would like the main tasks (100/200/300 etc to be formatted all one color (light blue)). I would like the next level sub tasks (100.10, 100.20, 100.30, etc.) to all be colored a different color (say beige). and the remainder (220.14, 220.15, etc,)to be left as is. I know this can be done with conditional formatting on integers, but I have no idea how to achieve this.


#2 - see the HR Tab and Column E - I would like that column to be shaded Red if: D is not 100 and H is a date in the past. See cell E9 and advise if that is the best option? I would then like to really move it to infornt of A and have it be a flag for the row. Does that sound good?

And, if that is a good idea, can you tell me if I could create another Column in front of Column A that would read the data in Column A and do If if is a solid 100 - 200 - 300 Turn the whole Row blue and if it is 110, 120, 130 turn it beige but if it is NOT a whole number leave it alone? Is there a way to do that? If so can you please tell me how.

Feel free to add the formulas -or conditional formatting into the document.

Please help! And thank you all so very much. Excel is amazing but I have no education in it all.

Mod edit: Attachment removed at OP's request. Please see subsequent posts for an updated file
 
Last edited by a moderator:
Are you sure your file contains no confidential information?
The attached uses named formulas to build criteria which are then applied as CF rules.
 

Attachments

  • Conditional formatting for project chart.xlsx
    11.3 KB · Views: 4
Hello Peter, yes this is what I am looking for. Can you tell me how I would take the data / formula you have provided and apply it my spreadsheet? Do I make this a separate sheet? and then where do I put the formula? TIA
 
I have attached a sample of the sheet I am trying to modify
 

Attachments

  • Sample upload.xlsx
    28.1 KB · Views: 3
I use the worksheet to develop the formula and then use a defined name to refer to the formula. You are by no means obliged to follow that path and your conditional formatting was almost correct, the only problem being that the CF should be applied to the entire table.
 

Attachments

  • Conditionally formatted table.xlsx
    28.8 KB · Views: 5
Peter Thank you so much!! It took me a very long time to get it, but I have finally gotten these formulas worked out properly in my spreadsheet!!
I have one more thing I am trying to do......... i you look at the sample upload above, in the task number column. I have a master tab that pulls in all these other spreadsheets. When I do the refresh, the task numbers do not stay the way I want them. I would like to trunc after the decimal if the number is a whole number, but show the decimals if not. I can see how to truc to 1 or 2 places, but not a if >than zero show if = to zero hide. is there a formula for that?

TIA!!!!!!
Mary
 
@marymary
The number format 'General' does what you describe.

Alternatively, set the number format to two decimal places and then use the blue and beige conditional formats that you already have to set the number format to no decimal places for those particular rows (in addition to setting the 'fill' set the 'number format'). That course of action will have the effect of giving the sequence
230.19
230.20
230.21

rather than having 230.2 in the middle.
 

Attachments

  • Conditional formatting for project chart.xlsx
    28.6 KB · Views: 3
Last edited:
Back
Top