• 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.

Conditional Format (Underline) based on a running balance total

U

Uri Teitler

New Member
I have a table with multiple cols and a running balance.
The detail has a number of projects with their costs and various prioritisation attributes.
We have set a budget for the year.
I would like to 'draw a line' (ie conditional format format border bottom red) when the running balance reaches the budget.

eg Budget = 30.0

A B C D E
Project A P1 M 10.0 10.0
Project B P1 S 10.0 20.0
Project C P1 C 10.0 30.0
Project D P2 M 10.0 40.0
Project E P2 S 10.0 50.0

I can do a simple conditional cell value <= 30 on E and make the all cells a colour.
Just don't know how to a) only make the last cell change and b) reference the E cell for the other cols.
 
This requires conditional formatting based upon a formula. The formula needs to test two conditions. The current value needs to meet the condition but the preceding one must fail it.
=(Sheet1!$E8<30)*(Sheet1!$E9>=30)
My development process would be to test the condition on the worksheet first (it should give a column of 0s with a single 1). Then I put the formula into the 'Refers to' box of a defined name, 'balanceReached?' for example.

upload_2018-9-3_10-7-28.png

That can also be written to the worksheet to check it will apply the conditional formatting to the correct row. From there, it is just a case of defining the conditional format.

upload_2018-9-3_10-5-28.png

I hope this makes sense. It is possible just to type a formula into the conditional format box but there are many ways of getting it wrong.
 
Uri Teitler
You wanted to highlite then just 30 ...
Select range
Conditional Formatting... >>
Classic Style
Use a formula to determine which cells to format
formula =$E1=30
>> make needed formatting
 
While I think of it; sometimes a bit of lateral thinking helps. To get thick underlines or double underline you would need to negate the process and apply the red formatting to every row. Then the conditional formatting would revert the format back to something less exuberant for every row except the budget reached row.
 
You must log in or register to reply here.
Back
Top