Possible Calculation depending the criterias

I used Excel tables, Pivot Tables and VBA to develop this application.

I created a table for the rates on the Rates Sheet. From now on when you need to manage rates, use this table (blue table). You only need to fill in Columns A:I. Column J is calculated automatically. If you change any information on this table, then refresh one of the pivot tables on the pivot table sheet. Since the tables are all made from the same source, refreshing one refreshes them all. You only have to do this when you change data in the green table.

Likewise, manage the season dates using the orange table. I note that you do not have seasons defined for Nov 1 to Apr 30.

Columns R and S are "helper columns" that do some intermediate calculations in Excel so I don't have to do them in the code.

The rates sheet can be hidden whenever you are not making updates to it.

The pivots sheet may also be hidden. This sheet is used to manage the drop down lists. For example, if you select BED & Breakfast s the Board Type, only Accommodations for Bed & Breakfast will be available for that drop down list. The same logic is used once an accommodation is chosen. The views are limited to that specific accommodation.

Which brings us back to Sayfa1. You get to fill in the green-shaded cells. The tan-shaded cells are computed automatically.
- Check-In is limited to May 1 through Oct 31. This value is driven from the orange table on the Rates Sheet.
- Check-out is limited to check in date through Oct 31.
- Number of days is calculated from check-in and check-out dates.

Note: As you make selections, the rate may indicate #N/A. This happens because until you complete all three selections, you may have an invalid combination of Board, Accommodation and Room Type. Once you complete all three selections, you should find the proper rate.

- Board is limited to the board types in the rates (green) table on the rates sheet. If you add a new type, it will show up on this list automatically.
- Accommodation type is limited based on the selection made for Board
- View is limited based on previous two selections.
- Number of Room is limited to a whole number greater than zero

Based on your selections, the program calculates the season and looks up the rate. The total is days * rate * number of rooms.

Somehow I got the impression that the rate was based on date of check out. I think I see what you want to do. I'm fairly certain I can make that happen.

I think I got it. The only thing I didn't do is extend the logic in case a person stayed more than two seasons. I would think you would want to collect at the end of the second season anyway.