Hi nba,

1. Name three ranges: "Low", "Medium", "High"

Example of the "Low" range (8 columns, as many rows as there are units):

sun mon tue wed thu fri sat
unit 1 100 90 90 90 90 100 100
unit 2 150 100 100 100 100 150 150

2. Name range "Calendar_365_L_M_H" (all 365 rows, 2 columns)

Example of this range:

9/1/2009 Low
9/2/2009 High
9/3/2009 Medium
9/4/2009 Medium
9/5/2009 Medium
9/6/2009 Low
9/7/2009 High

Range Name Example of data

"Unit" "Unit 1"
"Check_in" 9/2/2009
"Check_out" 9/7/2009

in an empty cell -

a10: =check_in
a11:=IF(A10="","",IF(A10+1check_out-1,"",A10+1))
copy cell A11 down to enough rows that will cover the number of days
that the most a reservation will last for.
b10:=IF(A10="","",VLOOKUP(Unit,INDIRECT(VLOOKUP(A1 0,Calendar_365_L_M_H,2,FALSE)),WEEKDAY(A10)+1))
copy cell b10 down to enough ros that will cover the number of days
that the most a reservation will last for.

Sum(b10:b#####)

This will give you the amount for the reservation.

Hope this helps,

Peggy

"nba" wrote:

I have 2 sheets. One with 365 dates showing low, mid or high season. The
second sheet has 3 tables listing multiple units with different pricing for
each day of the week for each season. EG:

table 2 rates low season
sun mon tue wed thu fri sat
unit 1 100 90 90 90 90 100 100
unit 2 150 100 100 100 100 150 150

I am using
VLOOKUP(H9,INDIRECT(VLOOKUP(F11,Dates,2,FALSE)),WE EKDAY(D11)+2,FALSE) to find
the right rate for the unit (H9) for the right date but now i need to add
each night of the booking for example 5 nights starting wed in unit 1.

that would be 90+90+100+100+100=480

can you help.