Forum Discussion

Hogstad_Raadgivning's avatar
Hogstad_Raadgivning
Iron Contributor
Nov 12, 2019

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...
conditional formating
excel
JKPieterse's avatar
JKPieterse
Silver Contributor
Nov 12, 2019
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's avatar
    Hogstad_Raadgivning
    Iron Contributor
    Nov 12, 2019

    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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 12, 2019

      Hogstad_Raadgivning 

      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.

      • Hogstad_Raadgivning's avatar
        Hogstad_Raadgivning
        Iron Contributor
        Nov 12, 2019

        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

Resources