Get a value based on 3 conditions

Can you provide a sample workbook, rather than contributors setting it up from scratch? To attach a file to one of your posts, click on Go Advanced whilst in Edit mode and scroll down to Manage Attachments, then click on Add Files | Select Files, then navigate to your file and double-click it, then click on Upload Files | Done and finally click on Submit Post.

Hope this helps.

Pete

Thanks for that Pete... Done

If you are working things out by days, you need to consider if your booking dates are inclusive of the start and end dates, i.e. if someone books from 23rd March to 30th March, is that 7 days or 8 ?

Pete

Hi Pete... we work by slept night... that example would be 7 nights

Okay, another question: someone wants to book for one night, from 22nd March to 23rd March - which period would that one night fall into?

Pete

We have a minimum booking period of 7 nights... but for the calculation purposes, it would still fall into the 1st period.

anyone able to help, please? at least pointing me in the right direction...

I did a bit of work on this last week, and then forgot about it as I had people to stay over the weekend. I would suggest splitting the days booked into a number of consecutive periods (probably 3 would be enough to cover long bookings), and then multiply the days by the rate in each period. It might be better to do this in a separate worksheet, so that you have one for bookings and one for the various prices.

You said that you have 8 pricing periods, but the example you posted had only 4, so I couldn't test it out completely. Also, you said you had 50 properties, but there was a smaller number in the example. Please attach another example which shows all the periods and the properties, and I'll work on the solution further (might be delayed a bit over Christmas).

Pete

Hi Pete... thanks for the help...

I'm also currently on holidays so when i return after new year, i'll post the full list.

Wishing a great Christmas and a Happy New Year to everyone here.

Here's the full list

Thanks for your help

Hi,

I was away yesterday and have just got back - I'll take a look at this later on.

Pete

Hi Pete... Any news or clue on where i could start solving this headhacke?

Thanks

See attached file. I've set up a simple booking sheet, although you might need to record other things like the name and contact details. Choose the property using the drop-down in column A, and enter the start date and end date in columns B and C. Formulae will then split the nights booked into the appropriate period in the next 8 columns. A further 8 columns return the nightly-charge for each period, and then these are multiplied together in column W to give a total for the booking. The formulae are copied down to row 50 - just copy them further if required.

Hope this helps.

Pete

Hi Pete... Seems absolutely PERFECT!!!... Running some tests but so far, everything seems ok

Thanks you so much...

Glad to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Thanks again for the help Pete... It is close to perfect...
Just getting some errors now when properties do not have defined values on some of the time brackets (means they are not available for rent on those brackets but that's not realy bothering me to be honest...

Also thanks for letting me know how to "Thank You" properly in terms of these Forums.

Pete... just a quick one after some tests:

If i need to add a property to the list, let's say DD309C... what steps do i need to respect? i tried just adding a line in the correxpondent row, but it doesn't seem to be working properly.

NVM Pete... got it... i missed the list at the end of the price chart on the first page

Yes, I didn't know if you had any duplicates in your table, so I set up a separate list and used that as the source for the DV drop-downs. I didn't make it a dynamic list, though, as I didn't think you will be adding extra properties to the list.

Pete