Calculation depending the criterias

[zrs]

Hi,

My excel table is attached. The areas on the top involved the criterias to get the sum on TOTAL area defined with details on the right. My questions can be summarized as:
1) How can the data in B3, B4 & B5 auto populated regarding the data below?
2) How can the sum be auto calculated depending the criterias in B1:B6 considering the broken periods (explained in D7 & 8).

Thanks for kindest promts.

 

[vletm]

zrs
The 1st sample version ...
... not fully tested!

 

[zrs]

Dear Vletm, Thanks for your kindest reply calculation in B7 seems worked well at my several tests. I made some changes on file (can be assumed as final file) and attached here. So can it be possible to adobt your vba according to this file? Details:

- I added OVERNIGHT Row below of dates and request to work with circular reference to CHECK-OUT Date means to auto calculate no of nights if CHECK-OUT is entered or to write the CHECK-OUT Date automatically when OVERNIGHT is given?

- Your defining of Periods can be more complicated for our users since they can not see End Date so I changed here more ease to see clearly. Can you adobt your vba depending these Periods format? PS: The calculation is failed in October could not see the prices.

However I would like to remind that Periods may go longer (12 columns) and Accommodation Types may be dilatable (Eg: 3ADT+2CHD (0-02)(0-02) & more varieties) with respect to hotel conditions.

Hope to hearing from you soon. Regards

 

[vletm]

zrs
Q: Do that 'Sayfa1' - TEST HOTEL-data to be in that sheet?
You need ONLY 'range A1:B8' there or how?

OVERNIGHT --- no matter nor no need nor not use!
Period End --- where needs? if next period always starts from next day!
Periods --- there could be as many periods as needed!
dropdowns --- all possible items have to be in 'Sayfa2'-sheet
Cell D7-note --- there can be 'any length of check-in/out'! -- no matter number of periods!

No changes before Your answers/comments...!

 

[zrs]

Hi.
A- Yes it will be in that sheet. Hotel data (prices, etc) is picked from another workbook via vba and formatted as in Sayfa-1. A1:B8 is the area to enter the parameters to find accurate price for that term.
A- I added OVERNIGHT area to be away from user failures because some requests are given as from 05.05 8 nights and user can be mistaken entering the right CHECK-OUT Date for 8 nights so this way we may get a possibility to make double check against errors.
A- Period End is also good not only for users but also for customers to see a specific period clearly. Sometimes customer may change travel date to utilize low rates than the period they choosed primary.
The rest is OK as you stated in your post. Regards

 

[vletm]

zrs
I continue and made 'some' modifications
... many parts are NOT READY!
Try to check ...

 

[zrs]

Hi. I checked seems making right calculation. As far as I see you transposed the data in Data Sheet. Hope to hear soon. Regards

 

[vletm]

zrs
'View's right top chart should be something You would like to see too .. or how?

 

[zrs]

Thanks indeed really seems nice. But honestly no need for a chart because this sheet is needed to provide instant calculation when a customers ask for holiday. For new terms or new customers I must make a new calculation and so on. I do want to state what I try to manage with your help: I have contracts with several hotels involving different periods & accommodation types which is prepared with another workbook so I have to run a vba to create a new workbook and assign the rates from contract workbook to a newone formatted as in my prior file "Example CF". Afterall I am collecting all these files in a folder and run your vba to calculate the requested hotel price according to the information provided by the client (Eg: Between .... - ..... dates, 2 Adult + 1 Chd ... y.o. in Sea View Room, etc). I am attaching pdf converted file of an original contract to see what I mean.

 

[vletm]

#5 Reply: = Chart =
A- Period End is also good not only for users but also for customers to see a specific period clearly. Sometimes customer may change travel date to utilize low rates than the period they choosed primary.
= those can see VISUALLY from Chart
With lower part, You could save/select saved calculations...
>> Your original file's layout is ... a bit different than Your ... PDF! <<

...hmm?​

If You ask to write something ... then You could get something but
if You've asked something else ... then You could get something else.
>>
I would be possible to use those something for Your something else!

Many things are possible ... but gotta know WHAT!

My the newest version of Your something ...
(there are some new feature which would work with something)

Ideas?​

 

[zrs]

Become more complicated than my expectation. Your file is nice to see prior calculations but can not understand - & + & Stress Buttons performance maybe I am missing something. However I was failed sometimes to enter new dates to be calculated gave some Warnings.
Dear my pdf file (normally it' s an .xlsm file just save as pdf for your concern) is original contract by the hotel to be signed mutually. But I can not use like that too small and mixed for my staff so need to run my macro convert it to a simple format attached here (final output). But in the middle of macro process I can extract the data as in my Example CF format then run your vba on this version for calculation. Maybe my English is not good enough to clarify, I mean my macro (I have working verion) will invert the data (periods, room types, boards, etc) from original contract to the format under TEST HOTEL. I also made some slight changes on your first file while you are working on new format also attached.

 

[vletm]

[ - ] & [ + ]
>> activate CheckIn or CheckOut date cell and press any of those buttons to change date to prev or next day

[ Stress ] ... if nothing happen .. then You'll maybe have a 'Stress'
... press it to try to help Yourself

'Dates' are not ready ... (as many other options)

Example ... hmm? I recommend my newer version!

.xls ... sorry
... I didn't get any idea? ... a lot of numbers
... means
... needed numbers will be hidden somewhere .... why?

as well as PDF ... hmm?

 

[zrs]

Sure your last version seems pretty to use. The .xls file is the final output file after running my macro to transfer hotel contract rates to a simple version. The dates under the letters A, B, C... are periods, the figures on yellow ribbon refers number of allotments (rooms to be reserved) that we can sell to customers. IN DBL PP: Per Person Rate in DBL room (needed to find price of dbl, single, triple or accommodations with kids), down of it all available accommodation types in this hotel are respectively defined with prices on the right. Regarding the hotel capacities and pricing policies Accommodation types can be multiplied Eg: 2ADT+2CHD, 3ADT+2CHD, etc. but may differ in each hotel. Release Days and other areas are referring optional days to inform the hotel to book and special discounts.