booking dynamic by day

  • Hello,


    Apparently, there is no need for a macro ...


    A formula in Column R could populate your data validation ...


    Is it what you are looking for ?


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    I need formula or VBA code for column H. Columns from K to N is example what I want to see in column H.


    Thanks

  • Hello again,


    Based on my understanding, your reference cells M16:M19 should display 28 and 33 ...


    Is this correct ... or not ... ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested changing the value in cell H3 and validating the formula in Column H ... feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim!


    I want to know for example what is happening on 11.11. or today?


    In the database you can see that all rooms in the Park Hotel have been canceled, so on 11.11. results must be 0 and not the sum of all the rooms.

  • Hello,


    Are you talking about your reference cells M16:M19 ...???


    Can you explain the logic behind the way you work out the numbers in Column M ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    If you are manually typing in all the numbers in your Columns M and N ....


    Then you should be able to test Column H without difficulty ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • What I want is how to set the formula in column H to be the results as in columns K:N.... so columns K:N don't exist here I just gave an example of how I want it to be in column H

  • What I want is how to set the formula in column H to be the results as in columns K:N.... so columns K:N don't exist here I just gave an example of how I want it to be in column H


    This is exactly what is included in your test file ...;)


    BUT all numbers do correspond ... with the exception of the ones in cells M16: M19 ...


    This is the reason why would appreciate your comment about my earlier question ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I wrote in the excel but here I will repeat.


    When I want to make a report on 15.5. for example in Hotel Park I have booked 10 rooms until 15.5. and next report will be on 30.5. then I have 4 rooms more, in total 14 rooms etc.

    But on 11.11. all rooms have been canceled so I have 0 so results must be 0 not 39.

  • In the post # 10 there was an interrogation


    Quote


    Can you explain the logic behind the way you work out the numbers in Column M ...?


    Would you mind specifically answering this question ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Your explanation needs to be clearer ...


    Are you after Calculations for Periodic Reports .... which go From one Start Date ... To another End Date ?


    For example : From 11 11 2021 To 26 11 2021 .... ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim!


    I mistakenly put 2021 so it might be confusing. Let's say 2020.

    We have the period 11.11-26.11.2020

    What I want is the following:

    On the report from 11.11 total is 0

    Someone booked 2 rooms on 12.11. and than the other one booked 4 rooms on 21.11., after that someone canceled 2 rooms on 25.11. (in the system it doesn't say Hotel Park -2 but it says 2 times Hotel Park 0 room, Hotel Park 0 room)

    I decided to make a report on 26.11 and see what happened in the booking since the last report which was 11.11.

    Results is not 2+4+0+0=6, results must be 2+4-0-0=0

  • So ... if I understand a bit more ... Things are getting complex ...


    Indeed, you do not have any field to report about any cancellation ....


    Or more precisely ... to indicate a cancellation for 3 nights :

    Instead of a record with a -3

    you do get 3 records showing 0

    Is that right ???


    If that the case, what do the dates of these records have in terms of signification ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)