Hi guys,

I posted a question a few weeks ago about a spreadsheet I have been working
on for my boss. Just when I thought I was home and dry, he has asked me to
add more to it and I am now stuck again.

It is to track hotel bookings and has been inputted like this:-

The headers begin in A4 and go as follows:- (Columns I-N all have a width
of zero so they appear hidden)

A - Reservation Number
B - Source of booking (dropdown box - Choices = Web, Worldspan, Pegasus,
Galileo, Amadeus, Sabre)
C - Arrival date (in the format DD/MM/YYYY)
D - Number of nights
E - Number of people
F - Total booking value
G - Average room rate (contains formula - Total booking value divided by
number of nights - F5 divided by D5)
H - Meals included (dropdown box)
I - Room revenue (contains formula - =IF($H8="Breakfast",$E8*6," ")
J - Breakfast Revenue (contains formula - =IF($H5="Breakfast",$E5*6," ")
K - Dinner Revenue (contains formula - =IF($H5="Dinner",$E5*17.95," ")
L - Dinner, bed and breakfast revenue (contains formula -
=IF($H5="DBB",$E5*23.95," ")
M - Room only (contains formula - =IF($H5="Room only",$E5*0," ")
N - Total food revenue (contains formula - =SUM(J5:M5)*D5
O - Net room revenue (Contains formula - =IF(F5=0,"",I5/1.175)
P - Net food Revenue (contains formula - =SUM(N5/1.175)
Q - Enhance revenue
R - Total net revenue (Contains formula - =SUM(O5:Q5)
S - Comission paid (contains formula)

All of the data is then entered underneath.

Now for the new bit he wants.

On a new sheet, he want a grid to show how much commision is being paid per
month to each provider of our bookings (column B - Source)

The new sheet that needs to be added will look like this:-

Columns:- (all in row 1)

B - April
C - May
D - June
E - July
F - August
G - September
H - October
I - November
J - December
K - January
L - February
M - March

In Column A from row 2 descending down

2 - Web
3 - Worldspan
4 - Pegasus
5 - Galileo
6 - Amadeus
7 - Sabre

So with all that in place, it needs to pickup the information from the
original sheet (named "Int Hot") so that all of the commission paid to the
source named "Web" in the month of April will appear in cell B2, Worldspan in
cell B3, etc.

Hope this makes sense and thanks in advance.
Matt

In B2 on the new sheet, try

=SUMPRODUCT(--('Int Hot'!$B$5:$B$22=Sheet2!$A2),--(TEXT('Int
Hot'!$C$5:$C$22,"mmmm")=Sheet2!B$1),'Int Hot'!$S$5:$S$22)

I'm matching the month based on the arrival date. You will need to adjust
the ending row in the ranges on Int Hot and replace Sheet2 with the correct
name for the new sheet. Then copy B2 across and down as needed.

Hope this helps,

Hutch

"Matt" wrote:

Hi guys,

I posted a question a few weeks ago about a spreadsheet I have been working
on for my boss. Just when I thought I was home and dry, he has asked me to
add more to it and I am now stuck again.

It is to track hotel bookings and has been inputted like this:-

The headers begin in A4 and go as follows:- (Columns I-N all have a width
of zero so they appear hidden)

A - Reservation Number
B - Source of booking (dropdown box - Choices = Web, Worldspan, Pegasus,
Galileo, Amadeus, Sabre)
C - Arrival date (in the format DD/MM/YYYY)
D - Number of nights
E - Number of people
F - Total booking value
G - Average room rate (contains formula - Total booking value divided by
number of nights - F5 divided by D5)
H - Meals included (dropdown box)
I - Room revenue (contains formula - =IF($H8="Breakfast",$E8*6," ")
J - Breakfast Revenue (contains formula - =IF($H5="Breakfast",$E5*6," ")
K - Dinner Revenue (contains formula - =IF($H5="Dinner",$E5*17.95," ")
L - Dinner, bed and breakfast revenue (contains formula -
=IF($H5="DBB",$E5*23.95," ")
M - Room only (contains formula - =IF($H5="Room only",$E5*0," ")
N - Total food revenue (contains formula - =SUM(J5:M5)*D5
O - Net room revenue (Contains formula - =IF(F5=0,"",I5/1.175)
P - Net food Revenue (contains formula - =SUM(N5/1.175)
Q - Enhance revenue
R - Total net revenue (Contains formula - =SUM(O5:Q5)
S - Comission paid (contains formula)

All of the data is then entered underneath.

Now for the new bit he wants.

On a new sheet, he want a grid to show how much commision is being paid per
month to each provider of our bookings (column B - Source)

The new sheet that needs to be added will look like this:-

Columns:- (all in row 1)

B - April
C - May
D - June
E - July
F - August
G - September
H - October
I - November
J - December
K - January
L - February
M - March

In Column A from row 2 descending down

2 - Web
3 - Worldspan
4 - Pegasus
5 - Galileo
6 - Amadeus
7 - Sabre

So with all that in place, it needs to pickup the information from the
original sheet (named "Int Hot") so that all of the commission paid to the
source named "Web" in the month of April will appear in cell B2, Worldspan in
cell B3, etc.

Hope this makes sense and thanks in advance.
Matt

Thanks Hutch, that works a treat.

However, the next challenge (as I was not thorough enough) is how can I make
it recognise booking in different years? I plan on making a Commissions
sheet for each financial year, but at the moment the commission for bookings
in both May 08 and May 09 are being picked whereas May 09 would fall under a
new financial year.

Do you know of any way to correct this?

Many thanks
Matt

"Tom Hutchins" wrote:

In B2 on the new sheet, try

=SUMPRODUCT(--('Int Hot'!$B$5:$B$22=Sheet2!$A2),--(TEXT('Int
Hot'!$C$5:$C$22,"mmmm")=Sheet2!B$1),'Int Hot'!$S$5:$S$22)

I'm matching the month based on the arrival date. You will need to adjust
the ending row in the ranges on Int Hot and replace Sheet2 with the correct
name for the new sheet. Then copy B2 across and down as needed.

Hope this helps,

Hutch

"Matt" wrote:

Hi guys,

I posted a question a few weeks ago about a spreadsheet I have been working
on for my boss. Just when I thought I was home and dry, he has asked me to
add more to it and I am now stuck again.

It is to track hotel bookings and has been inputted like this:-

The headers begin in A4 and go as follows:- (Columns I-N all have a width
of zero so they appear hidden)

A - Reservation Number
B - Source of booking (dropdown box - Choices = Web, Worldspan, Pegasus,
Galileo, Amadeus, Sabre)
C - Arrival date (in the format DD/MM/YYYY)
D - Number of nights
E - Number of people
F - Total booking value
G - Average room rate (contains formula - Total booking value divided by
number of nights - F5 divided by D5)
H - Meals included (dropdown box)
I - Room revenue (contains formula - =IF($H8="Breakfast",$E8*6," ")
J - Breakfast Revenue (contains formula - =IF($H5="Breakfast",$E5*6," ")
K - Dinner Revenue (contains formula - =IF($H5="Dinner",$E5*17.95," ")
L - Dinner, bed and breakfast revenue (contains formula -
=IF($H5="DBB",$E5*23.95," ")
M - Room only (contains formula - =IF($H5="Room only",$E5*0," ")
N - Total food revenue (contains formula - =SUM(J5:M5)*D5
O - Net room revenue (Contains formula - =IF(F5=0,"",I5/1.175)
P - Net food Revenue (contains formula - =SUM(N5/1.175)
Q - Enhance revenue
R - Total net revenue (Contains formula - =SUM(O5:Q5)
S - Comission paid (contains formula)

All of the data is then entered underneath.

Now for the new bit he wants.

On a new sheet, he want a grid to show how much commision is being paid per
month to each provider of our bookings (column B - Source)

The new sheet that needs to be added will look like this:-

Columns:- (all in row 1)

B - April
C - May
D - June
E - July
F - August
G - September
H - October
I - November
J - December
K - January
L - February
M - March

In Column A from row 2 descending down

2 - Web
3 - Worldspan
4 - Pegasus
5 - Galileo
6 - Amadeus
7 - Sabre

So with all that in place, it needs to pickup the information from the
original sheet (named "Int Hot") so that all of the commission paid to the
source named "Web" in the month of April will appear in cell B2, Worldspan in
cell B3, etc.

Hope this makes sense and thanks in advance.
Matt

The only concern I have with regards to your data setup for your Summary, is
if this workbook contains multiple years, the summary sheet would tabulate
April for multiple years. If your workbook is single year, then I don't see
an issue. Here is your formula for B2 on your summary sheet, copy down to B7,
and then to the right, copy your B2:B7 range all the way to M2:M7
B2: =SUMPRODUCT(--('Int Hot'!$B$5:$B$369=$A2),--(MONTH('Int
Hot'!$C$5:$C$369)=--TEXT(B$1&" 1, 2000","m")),('Int Hot'!$S$5:$S$369))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Matt" wrote:

Hi guys,

I posted a question a few weeks ago about a spreadsheet I have been working
on for my boss. Just when I thought I was home and dry, he has asked me to
add more to it and I am now stuck again.

It is to track hotel bookings and has been inputted like this:-

The headers begin in A4 and go as follows:- (Columns I-N all have a width
of zero so they appear hidden)

A - Reservation Number
B - Source of booking (dropdown box - Choices = Web, Worldspan, Pegasus,
Galileo, Amadeus, Sabre)
C - Arrival date (in the format DD/MM/YYYY)
D - Number of nights
E - Number of people
F - Total booking value
G - Average room rate (contains formula - Total booking value divided by
number of nights - F5 divided by D5)
H - Meals included (dropdown box)
I - Room revenue (contains formula - =IF($H8="Breakfast",$E8*6," ")
J - Breakfast Revenue (contains formula - =IF($H5="Breakfast",$E5*6," ")
K - Dinner Revenue (contains formula - =IF($H5="Dinner",$E5*17.95," ")
L - Dinner, bed and breakfast revenue (contains formula -
=IF($H5="DBB",$E5*23.95," ")
M - Room only (contains formula - =IF($H5="Room only",$E5*0," ")
N - Total food revenue (contains formula - =SUM(J5:M5)*D5
O - Net room revenue (Contains formula - =IF(F5=0,"",I5/1.175)
P - Net food Revenue (contains formula - =SUM(N5/1.175)
Q - Enhance revenue
R - Total net revenue (Contains formula - =SUM(O5:Q5)
S - Comission paid (contains formula)

All of the data is then entered underneath.

Now for the new bit he wants.

On a new sheet, he want a grid to show how much commision is being paid per
month to each provider of our bookings (column B - Source)

The new sheet that needs to be added will look like this:-

Columns:- (all in row 1)

B - April
C - May
D - June
E - July
F - August
G - September
H - October
I - November
J - December
K - January
L - February
M - March

In Column A from row 2 descending down

2 - Web
3 - Worldspan
4 - Pegasus
5 - Galileo
6 - Amadeus
7 - Sabre

So with all that in place, it needs to pickup the information from the
original sheet (named "Int Hot") so that all of the commission paid to the
source named "Web" in the month of April will appear in cell B2, Worldspan in
cell B3, etc.

Hope this makes sense and thanks in advance.
Matt

Thanks John,

I just asked the same question (I'll learn to refresh the page before I
respond next time)

I'm trying it now and will let you know.

Thankyou
Matt

"John C" wrote:

The only concern I have with regards to your data setup for your Summary, is
if this workbook contains multiple years, the summary sheet would tabulate
April for multiple years. If your workbook is single year, then I don't see
an issue. Here is your formula for B2 on your summary sheet, copy down to B7,
and then to the right, copy your B2:B7 range all the way to M2:M7
B2: =SUMPRODUCT(--('Int Hot'!$B$5:$B$369=$A2),--(MONTH('Int
Hot'!$C$5:$C$369)=--TEXT(B$1&" 1, 2000","m")),('Int Hot'!$S$5:$S$369))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Matt" wrote:

Hi guys,

I posted a question a few weeks ago about a spreadsheet I have been working
on for my boss. Just when I thought I was home and dry, he has asked me to
add more to it and I am now stuck again.

It is to track hotel bookings and has been inputted like this:-

The headers begin in A4 and go as follows:- (Columns I-N all have a width
of zero so they appear hidden)

A - Reservation Number
B - Source of booking (dropdown box - Choices = Web, Worldspan, Pegasus,
Galileo, Amadeus, Sabre)
C - Arrival date (in the format DD/MM/YYYY)
D - Number of nights
E - Number of people
F - Total booking value
G - Average room rate (contains formula - Total booking value divided by
number of nights - F5 divided by D5)
H - Meals included (dropdown box)
I - Room revenue (contains formula - =IF($H8="Breakfast",$E8*6," ")
J - Breakfast Revenue (contains formula - =IF($H5="Breakfast",$E5*6," ")
K - Dinner Revenue (contains formula - =IF($H5="Dinner",$E5*17.95," ")
L - Dinner, bed and breakfast revenue (contains formula -
=IF($H5="DBB",$E5*23.95," ")
M - Room only (contains formula - =IF($H5="Room only",$E5*0," ")
N - Total food revenue (contains formula - =SUM(J5:M5)*D5
O - Net room revenue (Contains formula - =IF(F5=0,"",I5/1.175)
P - Net food Revenue (contains formula - =SUM(N5/1.175)
Q - Enhance revenue
R - Total net revenue (Contains formula - =SUM(O5:Q5)
S - Comission paid (contains formula)

All of the data is then entered underneath.

Now for the new bit he wants.

On a new sheet, he want a grid to show how much commision is being paid per
month to each provider of our bookings (column B - Source)

The new sheet that needs to be added will look like this:-

Columns:- (all in row 1)

B - April
C - May
D - June
E - July
F - August
G - September
H - October
I - November
J - December
K - January
L - February
M - March

In Column A from row 2 descending down

2 - Web
3 - Worldspan
4 - Pegasus
5 - Galileo
6 - Amadeus
7 - Sabre

So with all that in place, it needs to pickup the information from the
original sheet (named "Int Hot") so that all of the commission paid to the
source named "Web" in the month of April will appear in cell B2, Worldspan in
cell B3, etc.

Hope this makes sense and thanks in advance.
Matt

My question is, where on your summary sheet are you referring to the year?
Both my formula and Tom's formula are easy enough to modify, but where are
you determining which year you want?
--
** John C **

"Matt" wrote:

Thanks John,

I just asked the same question (I'll learn to refresh the page before I
respond next time)

I'm trying it now and will let you know.

Thankyou
Matt

"John C" wrote:

The only concern I have with regards to your data setup for your Summary, is
if this workbook contains multiple years, the summary sheet would tabulate
April for multiple years. If your workbook is single year, then I don't see
an issue. Here is your formula for B2 on your summary sheet, copy down to B7,
and then to the right, copy your B2:B7 range all the way to M2:M7
B2: =SUMPRODUCT(--('Int Hot'!$B$5:$B$369=$A2),--(MONTH('Int
Hot'!$C$5:$C$369)=--TEXT(B$1&" 1, 2000","m")),('Int Hot'!$S$5:$S$369))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Matt" wrote:

Hi guys,

I posted a question a few weeks ago about a spreadsheet I have been working
on for my boss. Just when I thought I was home and dry, he has asked me to
add more to it and I am now stuck again.

It is to track hotel bookings and has been inputted like this:-

The headers begin in A4 and go as follows:- (Columns I-N all have a width
of zero so they appear hidden)

A - Reservation Number
B - Source of booking (dropdown box - Choices = Web, Worldspan, Pegasus,
Galileo, Amadeus, Sabre)
C - Arrival date (in the format DD/MM/YYYY)
D - Number of nights
E - Number of people
F - Total booking value
G - Average room rate (contains formula - Total booking value divided by
number of nights - F5 divided by D5)
H - Meals included (dropdown box)
I - Room revenue (contains formula - =IF($H8="Breakfast",$E8*6," ")
J - Breakfast Revenue (contains formula - =IF($H5="Breakfast",$E5*6," ")
K - Dinner Revenue (contains formula - =IF($H5="Dinner",$E5*17.95," ")
L - Dinner, bed and breakfast revenue (contains formula -
=IF($H5="DBB",$E5*23.95," ")
M - Room only (contains formula - =IF($H5="Room only",$E5*0," ")
N - Total food revenue (contains formula - =SUM(J5:M5)*D5
O - Net room revenue (Contains formula - =IF(F5=0,"",I5/1.175)
P - Net food Revenue (contains formula - =SUM(N5/1.175)
Q - Enhance revenue
R - Total net revenue (Contains formula - =SUM(O5:Q5)
S - Comission paid (contains formula)

All of the data is then entered underneath.

Now for the new bit he wants.

On a new sheet, he want a grid to show how much commision is being paid per
month to each provider of our bookings (column B - Source)

The new sheet that needs to be added will look like this:-

Columns:- (all in row 1)

B - April
C - May
D - June
E - July
F - August
G - September
H - October
I - November
J - December
K - January
L - February
M - March

In Column A from row 2 descending down

2 - Web
3 - Worldspan
4 - Pegasus
5 - Galileo
6 - Amadeus
7 - Sabre

So with all that in place, it needs to pickup the information from the
original sheet (named "Int Hot") so that all of the commission paid to the
source named "Web" in the month of April will appear in cell B2, Worldspan in
cell B3, etc.

Hope this makes sense and thanks in advance.
Matt

I think will need to make a grid for each financial year (probably up to 5
years in advance). Assuming it is possible to make it pick up the different
years, would it be exactly the same formula in all 5 grids, or would it need
modifying slightly?

The bookings are in the order in which we receive them, so the arrival dates
can vary dramatically, for example:- line 5 can have a booking for
15/11/2008, Line 6 can have 23/07/2009 and line 7 can have 07/12/2008 so
there is no order to the bookings. It may seem silly, but it is easier for
us to cross reference them at our end this way.

Hope that isn't too complicated

Muchly appreciated
Matt

"John C" wrote:

My question is, where on your summary sheet are you referring to the year?
Both my formula and Tom's formula are easy enough to modify, but where are
you determining which year you want?
--
** John C **

"Matt" wrote:

Thanks John,

I just asked the same question (I'll learn to refresh the page before I
respond next time)

I'm trying it now and will let you know.

Thankyou
Matt

"John C" wrote:

The only concern I have with regards to your data setup for your Summary, is
if this workbook contains multiple years, the summary sheet would tabulate
April for multiple years. If your workbook is single year, then I don't see
an issue. Here is your formula for B2 on your summary sheet, copy down to B7,
and then to the right, copy your B2:B7 range all the way to M2:M7
B2: =SUMPRODUCT(--('Int Hot'!$B$5:$B$369=$A2),--(MONTH('Int
Hot'!$C$5:$C$369)=--TEXT(B$1&" 1, 2000","m")),('Int Hot'!$S$5:$S$369))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Matt" wrote:

Hi guys,

I posted a question a few weeks ago about a spreadsheet I have been working
on for my boss. Just when I thought I was home and dry, he has asked me to
add more to it and I am now stuck again.

It is to track hotel bookings and has been inputted like this:-

The headers begin in A4 and go as follows:- (Columns I-N all have a width
of zero so they appear hidden)

A - Reservation Number
B - Source of booking (dropdown box - Choices = Web, Worldspan, Pegasus,
Galileo, Amadeus, Sabre)
C - Arrival date (in the format DD/MM/YYYY)
D - Number of nights
E - Number of people
F - Total booking value
G - Average room rate (contains formula - Total booking value divided by
number of nights - F5 divided by D5)
H - Meals included (dropdown box)
I - Room revenue (contains formula - =IF($H8="Breakfast",$E8*6," ")
J - Breakfast Revenue (contains formula - =IF($H5="Breakfast",$E5*6," ")
K - Dinner Revenue (contains formula - =IF($H5="Dinner",$E5*17.95," ")
L - Dinner, bed and breakfast revenue (contains formula -
=IF($H5="DBB",$E5*23.95," ")
M - Room only (contains formula - =IF($H5="Room only",$E5*0," ")
N - Total food revenue (contains formula - =SUM(J5:M5)*D5
O - Net room revenue (Contains formula - =IF(F5=0,"",I5/1.175)
P - Net food Revenue (contains formula - =SUM(N5/1.175)
Q - Enhance revenue
R - Total net revenue (Contains formula - =SUM(O5:Q5)
S - Comission paid (contains formula)

All of the data is then entered underneath.

Now for the new bit he wants.

On a new sheet, he want a grid to show how much commision is being paid per
month to each provider of our bookings (column B - Source)

The new sheet that needs to be added will look like this:-

Columns:- (all in row 1)

B - April
C - May
D - June
E - July
F - August
G - September
H - October
I - November
J - December
K - January
L - February
M - March

In Column A from row 2 descending down

2 - Web
3 - Worldspan
4 - Pegasus
5 - Galileo
6 - Amadeus
7 - Sabre

So with all that in place, it needs to pickup the information from the
original sheet (named "Int Hot") so that all of the commission paid to the
source named "Web" in the month of April will appear in cell B2, Worldspan in
cell B3, etc.

Hope this makes sense and thanks in advance.
Matt

I will post more tomorrow. Quick question if you get it. Is your Fiscal Year
from April to March? Or how are your months going to run on your summation
sheet?
--
** John C **

"Matt" wrote:

I think will need to make a grid for each financial year (probably up to 5
years in advance). Assuming it is possible to make it pick up the different
years, would it be exactly the same formula in all 5 grids, or would it need
modifying slightly?

The bookings are in the order in which we receive them, so the arrival dates
can vary dramatically, for example:- line 5 can have a booking for
15/11/2008, Line 6 can have 23/07/2009 and line 7 can have 07/12/2008 so
there is no order to the bookings. It may seem silly, but it is easier for
us to cross reference them at our end this way.

Hope that isn't too complicated

Muchly appreciated
Matt

"John C" wrote:

My question is, where on your summary sheet are you referring to the year?
Both my formula and Tom's formula are easy enough to modify, but where are
you determining which year you want?
--
** John C **

"Matt" wrote:

Thanks John,

I just asked the same question (I'll learn to refresh the page before I
respond next time)

I'm trying it now and will let you know.

Thankyou
Matt

"John C" wrote:

The only concern I have with regards to your data setup for your Summary, is
if this workbook contains multiple years, the summary sheet would tabulate
April for multiple years. If your workbook is single year, then I don't see
an issue. Here is your formula for B2 on your summary sheet, copy down to B7,
and then to the right, copy your B2:B7 range all the way to M2:M7
B2: =SUMPRODUCT(--('Int Hot'!$B$5:$B$369=$A2),--(MONTH('Int
Hot'!$C$5:$C$369)=--TEXT(B$1&" 1, 2000","m")),('Int Hot'!$S$5:$S$369))
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Matt" wrote:

Hi guys,

I posted a question a few weeks ago about a spreadsheet I have been working
on for my boss. Just when I thought I was home and dry, he has asked me to
add more to it and I am now stuck again.

It is to track hotel bookings and has been inputted like this:-

The headers begin in A4 and go as follows:- (Columns I-N all have a width
of zero so they appear hidden)

A - Reservation Number
B - Source of booking (dropdown box - Choices = Web, Worldspan, Pegasus,
Galileo, Amadeus, Sabre)
C - Arrival date (in the format DD/MM/YYYY)
D - Number of nights
E - Number of people
F - Total booking value
G - Average room rate (contains formula - Total booking value divided by
number of nights - F5 divided by D5)
H - Meals included (dropdown box)
I - Room revenue (contains formula - =IF($H8="Breakfast",$E8*6," ")
J - Breakfast Revenue (contains formula - =IF($H5="Breakfast",$E5*6," ")
K - Dinner Revenue (contains formula - =IF($H5="Dinner",$E5*17.95," ")
L - Dinner, bed and breakfast revenue (contains formula -
=IF($H5="DBB",$E5*23.95," ")
M - Room only (contains formula - =IF($H5="Room only",$E5*0," ")
N - Total food revenue (contains formula - =SUM(J5:M5)*D5
O - Net room revenue (Contains formula - =IF(F5=0,"",I5/1.175)
P - Net food Revenue (contains formula - =SUM(N5/1.175)
Q - Enhance revenue
R - Total net revenue (Contains formula - =SUM(O5:Q5)
S - Comission paid (contains formula)

All of the data is then entered underneath.

Now for the new bit he wants.

On a new sheet, he want a grid to show how much commision is being paid per
month to each provider of our bookings (column B - Source)

The new sheet that needs to be added will look like this:-

Columns:- (all in row 1)

B - April
C - May
D - June
E - July
F - August
G - September
H - October
I - November
J - December
K - January
L - February
M - March

In Column A from row 2 descending down

2 - Web
3 - Worldspan
4 - Pegasus
5 - Galileo
6 - Amadeus
7 - Sabre

So with all that in place, it needs to pickup the information from the
original sheet (named "Int Hot") so that all of the commission paid to the
source named "Web" in the month of April will appear in cell B2, Worldspan in
cell B3, etc.

Hope this makes sense and thanks in advance.
Matt

Matt wrote:
Hi guys,

I posted a question a few weeks ago about a spreadsheet I have been working
on for my boss. Just when I thought I was home and dry, he has asked me to
add more to it and I am now stuck again.

It is to track hotel bookings and has been inputted like this:-

The headers begin in A4 and go as follows:- (Columns I-N all have a width
of zero so they appear hidden)

A - Reservation Number
B - Source of booking (dropdown box - Choices = Web, Worldspan, Pegasus,
Galileo, Amadeus, Sabre)
C - Arrival date (in the format DD/MM/YYYY)
D - Number of nights
E - Number of people
F - Total booking value
G - Average room rate (contains formula - Total booking value divided by
number of nights - F5 divided by D5)
H - Meals included (dropdown box)
I - Room revenue (contains formula - =IF($H8="Breakfast",$E8*6," ")
J - Breakfast Revenue (contains formula - =IF($H5="Breakfast",$E5*6," ")
K - Dinner Revenue (contains formula - =IF($H5="Dinner",$E5*17.95," ")
L - Dinner, bed and breakfast revenue (contains formula -
=IF($H5="DBB",$E5*23.95," ")
M - Room only (contains formula - =IF($H5="Room only",$E5*0," ")
N - Total food revenue (contains formula - =SUM(J5:M5)*D5
O - Net room revenue (Contains formula - =IF(F5=0,"",I5/1.175)
P - Net food Revenue (contains formula - =SUM(N5/1.175)
Q - Enhance revenue
R - Total net revenue (Contains formula - =SUM(O5:Q5)
S - Comission paid (contains formula)

All of the data is then entered underneath.

Now for the new bit he wants.

On a new sheet, he want a grid to show how much commision is being paid per
month to each provider of our bookings (column B - Source)

The new sheet that needs to be added will look like this:-

Columns:- (all in row 1)

B - April
C - May
D - June
E - July
F - August
G - September
H - October
I - November
J - December
K - January
L - February
M - March

In Column A from row 2 descending down

2 - Web
3 - Worldspan
4 - Pegasus
5 - Galileo
6 - Amadeus
7 - Sabre

So with all that in place, it needs to pickup the information from the
original sheet (named "Int Hot") so that all of the commission paid to the
source named "Web" in the month of April will appear in cell B2, Worldspan in
cell B3, etc.

Hope this makes sense and thanks in advance.
Matt


I would suggest using a PivotTable, and grouping dates by month.