Home

Conditional Formatting: cell colour changes when included in a formula elsewhere in the sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1014067%22%20slang%3D%22en-US%22%3EConditional%20Formatting%3A%20cell%20colour%20changes%20when%20included%20in%20a%20formula%20elsewhere%20in%20the%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014067%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20conditional%20formatting%20the%20best%20way%20to%20easily%20identify%20which%20cells%20have%20been%20included%20in%20formulas%20elsewhere%20on%20a%20spreadsheet%3F%3C%2FP%3E%3CP%3EI'm%20trying%20to%20automatically%20colour%20code%20the%20cells%20in%20column%20A%20to%20indicate%20they%20have%20been%20included%20in%20either%20the%20income%20(column%20F)%20or%20expenses%20(column%20H).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1014067%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
cchristensen
Occasional Visitor

Is conditional formatting the best way to easily identify which cells have been included in formulas elsewhere on a spreadsheet?

I'm trying to automatically colour code the cells in column A to indicate they have been included in either the income (column F) or expenses (column H).

 

Many thanks!

 

 

1 Reply

@cchristensen 

Perhaps you want to consider reconstructing your schedule. To say the least, it's rather inconsistent and confusing. E.g., "Mums income" looks for "Salary" in column A. But for "Dad" it picks up "Salary" from column B. On the expense side, Mortgage Payments looks for the text "Loan Payment" in column A, where column B actually has "Mortgage" as a reference. And for Gas, you look for a specific supplier name in column B. etc. etc.

 

It would be more effective if you organise your date in columns similar to what is in the picture below, and do your analysis, summaries from there. And then there is no need for conditional formatting. I took the liberty to make up a few Organisation names to demonstrate my point.

Screenshot 2019-11-18 at 11.25.36.png

Related Conversations
What's new in Edge insider Canary Version 79.0.284
HotCakeX in Discussions on
20 Replies
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies