View original (French)

File with some lines merged and others not

Solved
cs_val78 Posted messages 80 Registration date   Status Member Last intervention   -  
cs_val78 Posted messages 80 Registration date   Status Member Last intervention   -

Hello,

I am creating a file with merged cells and some unmerged ones.

In some cells I managed to put weekends and holidays, but I can't apply them to the cells that are not merged.

Does anyone have an idea for me to enlighten me.

https://cijoint.org/r/0kXZnh30#Tx/8l726KaK7Ow+i9KH4zzYjSx6lrcnEz98a6Mvih30=

Here is the link to the file to help understanding.

Thank you and good evening

Valerie


4 answers

  1.
    via55 Posted messages 14396 Registration date   Status Member Last intervention   2 759
     

    Good evening

    It is always a bad idea to merge cells because it creates chaos in conditional formatting and macros

    Is there a way to achieve the same thing without merging cells, simply by showing or hiding the data according to the rows via conditional formatting

    https://cijoint.org/r/JV7V15K_#V5WGJPLhLW12yCxbOiYKPqLwXodfTL2IFIxw84xg9NU=

    Thus in columns B and C only every 3rd row (3, 6, 9...81) has its text appearing in black (or white for today's date), while for the others the text (date) exists but is written in white, green, orange or red by MFC depending on the case)

    This makes it easy to apply MFC for holidays and weekends for the following columns since every row has a date

    Best regards

    Via

    2
    1. Raymond PENTIER Posted messages 58229 Registration date   Status Contributor Last intervention   17 494
       

      Hello Via.

      It seems your file is corrupted.

      Could you send it again?

      Regards.

      1
      1. brucine Posted messages 25292 Registration date   Status Member Last intervention   4 193 > Raymond PENTIER Posted messages 58229 Registration date   Status Contributor Last intervention  
         

        Hello,

        I had the same problem.

        The cause may be that it isn’t an Excel file but a LibreOffice file with specific content (macros...) that crashes Excel on startup.

        In any case after this opening, you can clearly see a calendar made “by hand” (adding to an initial date) but nowhere is there anything that would allow recognizing weekends and holidays, except of course in the first case conditionally on the cell appearing Saturday or Sunday because that is its date format.

        -1
  2. via55 Posted messages 14396 Registration date   Status Member Last intervention   2 759
     

    Hello everyone

    No, my file is not corrupted I just retrieved it and opened it via the link

    On the other hand, Valérie's file is indeed an .ods and seems corrupted when opened with Excel. It must be opened with LibreOffice and then re-saved in Excel to be readable and workable

    My file is based on Valérie's from which I removed the merged cells before working on the MFC (completely agree with Daniel on the uselessness and the drawback of using those darn merged cells)

    2
  4. cs_val78 Posted messages 80 Registration date   Status Member Last intervention  
     

    Hello, I found the solution I hadn't used the correct syntax on

    JOURSEM($A2;2)>5

    NB.SI($L$3:$L$13;$A2)

    in both formulas I had $B2 instead of $A2

    Thanks to everyone for your help.

    0