Forum Discussion
Condtional formating is broken/changed when adding rows
Hi, 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) The Sce...
JKPieterse Herr is the spreadsheet. All data here is just for testing.
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.
JKPieterse Thank you for looking into this.
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.
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.
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.
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.
Best Regards
- Geir
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$2work on two tables in first sheet. If I understood your logic correctly.
SergeiBaklan Hello Sergei.
I need the table in the second sheet, to see if there any values in the green table for each empliyee. If there is another. better solution I am willing to learn 🙂
Please notice that the employees in the blue and green table is similar. But the blue table can have 2, or more rows for each employee.
So the table in the second sheet tells me if the person is available for project or not.
As mentioned, that part works. The problem is that the rules for conditional formating is ruined when I add, delete rows in the blue table. At least with this setup.
Best Regards
- Geir
