Condtional formating is broken/changed when adding rows

Question

Hi,&nbsp;My use of conditional formating works, but it is not otpimal. I wish it could be easier to use for the end user, to avoid errors and mistakes. (Second attempt, simplified version)&nbsp;The Scenario:1 Table with a set of tasks. If I find a specified word in that table, i would like to change the color in my second table.&nbsp;Original setup. Everything is fine:Deleting a line in my table.Conditional formating rules is rebuild. And the colors is moved up by a line. No longer matching the ID in the formula give.&nbsp;&nbsp;Adding a new row,&nbsp;Now the colored cells is pushed down a line. So Wrong persoon is colored.&nbsp;&nbsp;&nbsp;I cant understand the Reason for adding more conditions, instead of just change the area for the condition.&nbsp;&nbsp;So, is there a better solutions for this?&nbsp;Best Regards- Geir

jkpieterse · Answer

Can you attach the file (anonymized)? I think your CF formulas are wrong (this is something else than the "breaking up" of the CF rules, that is a bug in Excel)

hogstad_raadgivning · Answer

JKPieterse&nbsp;Herr is the spreadsheet. All data here is just for testing.&nbsp;&nbsp;

jkpieterse · Answer

Hmm, not a simple one to answer. I think the key problem is that you're referring to cells on another worksheet and that inserting rows on the main sheet does not affect the cell addresses pointing to the other sheet. But I have a hard time understanding what you are trying to do.

hogstad_raadgivning · Answer

JKPieterse&nbsp;Thank you for looking into this.&nbsp;First, the table I refere to in another sheet works. that is always a copy of the Blue table (see below). With text from the green table if 2 conditions are correct. AnsattID and the text from the green table.&nbsp;What the goal is: To change the color in the second table, if i a specified text is found in the first table.Reason: Table one is for Team leaders. They have first choice, and updte the employe.&nbsp;Second table is for project managers. If there is no specific on a given day for an employee , they can be used for projects/assigments.&nbsp;&nbsp;The problem is within Conditional formating. It add/removes rules, and it change the cell where the formula is located in Sheet 2. From B2.&nbsp;&nbsp;&nbsp;Best Regards- Geir

sergeibaklan · Answer

Hogstad_Raadgivning&nbsp;
Geir, I didn't catch why do you need the table in the second sheet. Rules like
=INDEX(INDIRECT("t_Team"),MATCH($B13,INDIRECT("t_Team[Ansatt ID]"),0),MATCH(D$12,INDIRECT("t_Team[#Headers]"),0))=$C$2
work on two tables in first sheet. If I understood your logic correctly.

Forum Discussion

Condtional formating is broken/changed when adding rows

Share

Resources