View original (French)

Search for a solution for a monthly schedule table in LibreOffice Calc.

Solved
LolaBosse Posted messages 7 Status Member -  
LolaBosse Posted messages 7 Status Member -
Hello,

I am new to the site and also new as an Excel user.
I have set up a workbook with 2 sheets that link to each other:

1) payment evidence
2) monthly schedule

The evidence sheet allows me to display messages in case of incomplete payment or unpaid rent established month by month and for each tenant. I am able to display the messages, but they show up for all months, whereas I would like these messages to only appear based on the current month (which seems more logical to me). The months are set up by columns, while the information comes in row by row based on the different people.

The formula is:
=IF($'monthly schedule 2019'.G49="", "RENT", IF($'monthly schedule 2019'.G49<$'monthly schedule 2019'.$C$49, "INCOMPLETE", ""))

The monthly schedule sheet includes all the information (names, amount, building) if the rent is entered in this sheet, the information is transcribed into the sheet named "payment evidence."

Could someone help me as I am unable to find a solution to my problem? I have browsed the forum but found no solution.

Thank you

Configuration: Windows / Chrome 72.0.3626.119

2 answers

  1.
    yclik Posted messages 3875 Registration date   Status Member Last intervention   1 611
     
    Hello
    An example of a depersonalized spreadsheet would be to upload it to Cjoint to get the best possible responses.
    How to upload to Cjoint.fr
    0
  2. ChrisPF13 Posted messages 234 Registration date   Status Member Last intervention   53
     
    Hello
    A little modified, but it works for me.
    https://www.cjoint.com/c/IBCszCa2Wcj
    Best regards
    0
    1. LolaBosse Posted messages 7 Status Member
       
      Good evening ChrisPF13,

      Thank you for the file, I just took a quick look at what you have set up. I still need to adapt it and make sure I understand it correctly, but I can already tell you that your work is perfect!!!

      One small question remains regarding the formula concerning the last part that shows the info "incomplete".
      I understand that cell D7 (which corresponds to the cell for the January schedule) is smaller than cell $A$10 (corresponding to the last cell in the amounts column in the evidence sheet) and that this is the case in all scenarios... this is where my question arises because I would have thought it should be linked with each row, but I saw that this cell is constant in the formula. Can you explain this to me, thank you.
      0
    2. ChrisPF13 Posted messages 234 Registration date   Status Member Last intervention   53
       
      Good evening
      You are right, it was a mistake on my part, I forgot to remove the $


      Best regards
      0
    3. LolaBosse Posted messages 7 Status Member
       
      Good evening

      Thank you very much.... it works except for 2 small things that I haven't been able to resolve:

      - if I indicate "type Month", I get a #NAME error (how do you do it?)
      - for unselected months, it displays: "0€"

      I can't see where I might have made a mistake in applying the formula???


      0
    4. LolaBosse Posted messages 7 Status Member
       
      Hello,

      Here is the problem solved for the display of €0, I just replaced the last part of the formula with "".

      Thank you, THANK YOU, I will adapt a conditional formatting color for rent and incomplete, and everything will be perfect.
      0
    5. ChrisPF13 Posted messages 234 Registration date   Status Member Last intervention   53
       
      The "month input" is in cell formatting, so it should be disregarded. It's just a reminder.
      You simply write the number 4, 5 ....12
      Normally, that's fine.
      For the €0, the cell needs to be formatted. Look at the first file linked and take back the formula.
      0