Booking record

[Martin]

Hello,

I'm trying to work out a method a showing graphically the bookings for
several meetings rooms.

I have the booking dates and room numbers and what I'd like is to show a
'year planner' style worksheet showing usage/availability. What is the best
method for this? I thought I could do a SUMPRODUCT and conditional format ie
if condition A is met (date) and condition B (meeting room number) then set
the conditional format to change the background. There will a sheet for each
meeting room and I intend to set a complete years' day by day calendar for
each room. The 'main' worksheet will contain complete details of all
bookings, purpose, special instructions etc. and will contain the source for
booking dates. The dates will not always come in sequentially (people may
book several months ahead and for different room numbers for example). One
of the problems I have is the dates; currently I record a 3 day booking from
the 15th (say) so my planner would have to show 15, 16, 17 as in use so if I
showed on the main sheet the start and end date and then did a >first date
and< last date in the date condition, would that work? Is there an easier
way (please!) than this?

Any tips would be gratefully received,

Ludek

 

[Mark Graesser]

Martin
This formula assumes the following

Box table is on Sheet1, with the date in row 1 and the rooms in column A
Main sheet is on Sheet2, with room in column A, date start in column B, and date finish in column

Box Table
1- Starting in B1 enter dates to the righ
2- Starting in A2 enter rooms dow
3- Enter this formula in B
=SUMPRODUCT((Sheet1!$A$1:$A$500=$A2)*(Sheet1!$B$1:$B$500<=B$1)*(Sheet1!$C$1:$C$500>=B$1)

The relative/absolute reference styles will allow this to be copyied down and across and maintain the proper references

Now if you enter data on the main table you will get zeros and ones in the box table. If you have a conflict you will get a number higher then 1

You can set the font color to white in order to hide the number. You can then use conditional formatting to change the color of the cells base on the number. This also gives you the benifit of highlighting any double bookings

If you would like a copy of the file a set up, repost with your email address in the text of your message, or e-mail me directly

Good Luck
Mark Graesse
(e-mail address removed)
----- Martin wrote: ----

Hello

I'm trying to work out a method a showing graphically the bookings fo
several meetings rooms

I have the booking dates and room numbers and what I'd like is to show
'year planner' style worksheet showing usage/availability. What is the bes
method for this? I thought I could do a SUMPRODUCT and conditional format i
if condition A is met (date) and condition B (meeting room number) then se
the conditional format to change the background. There will a sheet for eac
meeting room and I intend to set a complete years' day by day calendar fo
each room. The 'main' worksheet will contain complete details of al
bookings, purpose, special instructions etc. and will contain the source fo
booking dates. The dates will not always come in sequentially (people ma
book several months ahead and for different room numbers for example). On
of the problems I have is the dates; currently I record a 3 day booking fro
the 15th (say) so my planner would have to show 15, 16, 17 as in use so if
showed on the main sheet the start and end date and then did a >first dat
and< last date in the date condition, would that work? Is there an easie
way (please!) than this

Any tips would be gratefully received

Lude

 

[Mark Graesser]

I had the sheets backward

Should be
Main sheet on Sheet
Box table on sheet

----- Mark Graesser wrote: ----

Martin
This formula assumes the following

Box table is on Sheet1, with the date in row 1 and the rooms in column A
Main sheet is on Sheet2, with room in column A, date start in column B, and date finish in column

Box Table
1- Starting in B1 enter dates to the righ
2- Starting in A2 enter rooms dow
3- Enter this formula in B
=SUMPRODUCT((Sheet1!$A$1:$A$500=$A2)*(Sheet1!$B$1:$B$500<=B$1)*(Sheet1!$C$1:$C$500>=B$1)

The relative/absolute reference styles will allow this to be copyied down and across and maintain the proper references

Now if you enter data on the main table you will get zeros and ones in the box table. If you have a conflict you will get a number higher then 1

You can set the font color to white in order to hide the number. You can then use conditional formatting to change the color of the cells base on the number. This also gives you the benifit of highlighting any double bookings

If you would like a copy of the file a set up, repost with your email address in the text of your message, or e-mail me directly

Good Luck
Mark Graesse
(e-mail address removed)
----- Martin wrote: ----

Hello

I'm trying to work out a method a showing graphically the bookings fo
several meetings rooms

I have the booking dates and room numbers and what I'd like is to show
'year planner' style worksheet showing usage/availability. What is the bes
method for this? I thought I could do a SUMPRODUCT and conditional format i
if condition A is met (date) and condition B (meeting room number) then se
the conditional format to change the background. There will a sheet for eac
meeting room and I intend to set a complete years' day by day calendar fo
each room. The 'main' worksheet will contain complete details of al
bookings, purpose, special instructions etc. and will contain the source fo
booking dates. The dates will not always come in sequentially (people ma
book several months ahead and for different room numbers for example). On
of the problems I have is the dates; currently I record a 3 day booking fro
the 15th (say) so my planner would have to show 15, 16, 17 as in use so if
showed on the main sheet the start and end date and then did a >first dat
and< last date in the date condition, would that work? Is there an easie
way (please!) than this

Any tips would be gratefully received

Lude

 

[Ludek]

Hi Mark,

Well, that was easy then eh!?

Fantastic job, many thanks. It was the last part that I was struggling with.
I couldn't work out how to log the dates in between the start and end dates
but now I see your rather elegant solution it all becomes clear, many thanks
again.

At least I was on the right track even if I hadn't quite made the last lap!

ATB,

Martin

I had the sheets backward.

Should be:
Main sheet on Sheet 1
Box table on sheet 2

----- Mark Graesser wrote: -----

Martin,
This formula assumes the following:

Box table is on Sheet1, with the date in row 1 and the rooms in column A.
Main sheet is on Sheet2, with room in column A, date start in column B, and date finish in column C

Box Table:
1- Starting in B1 enter dates to the right
2- Starting in A2 enter rooms down
3- Enter this formula in B2
=SUMPRODUCT((Sheet1!$A$1:$A$500=$A2)*(Sheet1!$B$1:$B$500<=B$1)*(Sheet1!$C$1:
$C$500>=B$1))

The relative/absolute reference styles will allow this to be copyied

down and across and maintain the proper references.

Now if you enter data on the main table you will get zeros and ones

in the box table. If you have a conflict you will get a number higher then
1.

You can set the font color to white in order to hide the number. You

can then use conditional formatting to change the color of the cells base on
the number. This also gives you the benifit of highlighting any double
bookings.

If you would like a copy of the file a set up, repost with your email

address in the text of your message, or e-mail me directly.

 

[Mark Graesser]

Ludek
Glad to help. It was an interesting problem

Regards
Mark Graesse
(e-mail address removed)

----- Ludek wrote: ----

Hi Mark

Well, that was easy then eh!? :-

Fantastic job, many thanks. It was the last part that I was struggling with
I couldn't work out how to log the dates in between the start and end date
but now I see your rather elegant solution it all becomes clear, many thank
again

At least I was on the right track even if I hadn't quite made the last lap

ATB

Marti

I had the sheets backward
Main sheet on Sheet
Box table on sheet
This formula assumes the following column A
Main sheet is on Sheet2, with room in column A, date start in colum B, and date finish in column
1- Starting in B1 enter dates to the righ
2- Starting in A2 enter rooms dow
3- Enter this formula in B
=SUMPRODUCT((Sheet1!$A$1:$A$500=$A2)*(Sheet1!$B$1:$B$500<=B$1)*(Sheet1!$C$1
$C$500>=B$1)

in the box table. If you have a conflict you will get a number higher the
1can then use conditional formatting to change the color of the cells base o
the number. This also gives you the benifit of highlighting any doubl
bookings