Linking Cells
 

I created a spread sheet which acts as a rooming list. Columns have names,
in date out date and room type designated by a letter (theres are 4 types).
I'm going crazy trying to set a format which will automatically prepopulate
the room type code associated with a name and the dates into individual
columns (each one for a date within the span of the time people will be in
the hotel). At the bottom of each date column I have a total (which is
segmented into the various room types so that I can have a daily total sum
for each room type.

Linking Cells
 

If I understand what you want,
if your output is to be a table with room type across and date down
for example roometype indicator in B101-E101
and dates in A102-A200
in B102 enter
=sumproduct((Indate range-outdate
range),--(roomtype_range=B$101),--(indate_range<=$A102),--(outdate_range=$A102))
copy and paste to B102:E200
the --( changes the logical true false to a numeric 1 0
the arrays must be the same size but cannot reference a full column for pre
2007

"KWP" wrote:

I created a spread sheet which acts as a rooming list. Columns have names,
in date out date and room type designated by a letter (theres are 4 types).
I'm going crazy trying to set a format which will automatically prepopulate
the room type code associated with a name and the dates into individual
columns (each one for a date within the span of the time people will be in
the hotel). At the bottom of each date column I have a total (which is
segmented into the various room types so that I can have a daily total sum
for each room type.

Linking Cells
 

Hi BJ - I need the room types to fill under each date with the Room type
letter I have in the column which designates room types. WhenI enter this
formula, and then copy to paste I get
#NAME? in each cell. I think your answer is geeting me close to where I nedd
to be but I'm missing something. Please help me once again and many thanks.

KWP

--
"bj" wrote:

If I understand what you want,
if your output is to be a table with room type across and date down
for example roometype indicator in B101-E101
and dates in A102-A200
in B102 enter
=sumproduct((Indate range-outdate
range),--(roomtype_range=B$101),--(indate_range<=$A102),--(outdate_range=$A102))
copy and paste to B102:E200
the --( changes the logical true false to a numeric 1 0
the arrays must be the same size but cannot reference a full column for pre
2007

"KWP" wrote:

I created a spread sheet which acts as a rooming list. Columns have names,
in date out date and room type designated by a letter (theres are 4 types).
I'm going crazy trying to set a format which will automatically prepopulate
the room type code associated with a name and the dates into individual
columns (each one for a date within the span of the time people will be in
the hotel). At the bottom of each date column I have a total (which is
segmented into the various room types so that I can have a daily total sum
for each room type.

Linking Cells
 

Each guest has individual in/out dates, (two columns each one showing In-Date
Out-Date) then there is a column which designates room type (there are four
different room types represented by letter: J, D, E, C). Then there are
columns for dates from 3 June to 27 Jun. Each of these columns is actually
made up of three columns which have been merged because at the bottom of the
table the information feeds into totals (here the columns are shown
individually) and show totals for the respective date as Reserved,
Contracted, Difference.

What I want to be able to do is Automatically feed from the columns which
show in and out dates to the Individual Date columns and have those populated
with the letter code for the room type.

The solution you gave me does not work. Is ther some way I can show you the
work sheet I made up? Thank you once again.









"bj" wrote:

If I understand what you want,
if your output is to be a table with room type across and date down
for example roometype indicator in B101-E101
and dates in A102-A200
in B102 enter
=sumproduct((Indate range-outdate
range),--(roomtype_range=B$101),--(indate_range<=$A102),--(outdate_range=$A102))
copy and paste to B102:E200
the --( changes the logical true false to a numeric 1 0
the arrays must be the same size but cannot reference a full column for pre
2007

"KWP" wrote:

I created a spread sheet which acts as a rooming list. Columns have names,
in date out date and room type designated by a letter (theres are 4 types).
I'm going crazy trying to set a format which will automatically prepopulate
the room type code associated with a name and the dates into individual
columns (each one for a date within the span of the time people will be in
the hotel). At the bottom of each date column I have a total (which is
segmented into the various room types so that I can have a daily total sum
for each room type.

Linking Cells
 

I am not sure what you mean by populate the date columns
and am not sure what you mean by the columns around the dates being merged
I will be unavailable for a couple of days around the weekend, but will
contact you again next week.
check the sumproduct equation and try putting each segement ,divided by
commas, into an sumproduct equation to try to identify from where the error
is coming.
also check sumproduct in help. Some versions of excel need semicolons
instead of commas for seperators. the #name indicates a format prolem of
some type

"KWP" wrote:

Each guest has individual in/out dates, (two columns each one showing In-Date
Out-Date) then there is a column which designates room type (there are four
different room types represented by letter: J, D, E, C). Then there are
columns for dates from 3 June to 27 Jun. Each of these columns is actually
made up of three columns which have been merged because at the bottom of the
table the information feeds into totals (here the columns are shown
individually) and show totals for the respective date as Reserved,
Contracted, Difference.

What I want to be able to do is Automatically feed from the columns which
show in and out dates to the Individual Date columns and have those populated
with the letter code for the room type.

The solution you gave me does not work. Is ther some way I can show you the
work sheet I made up? Thank you once again.









"bj" wrote:

If I understand what you want,
if your output is to be a table with room type across and date down
for example roometype indicator in B101-E101
and dates in A102-A200
in B102 enter
=sumproduct((Indate range-outdate
range),--(roomtype_range=B$101),--(indate_range<=$A102),--(outdate_range=$A102))
copy and paste to B102:E200
the --( changes the logical true false to a numeric 1 0
the arrays must be the same size but cannot reference a full column for pre
2007

"KWP" wrote:

I created a spread sheet which acts as a rooming list. Columns have names,
in date out date and room type designated by a letter (theres are 4 types).
I'm going crazy trying to set a format which will automatically prepopulate
the room type code associated with a name and the dates into individual
columns (each one for a date within the span of the time people will be in
the hotel). At the bottom of each date column I have a total (which is
segmented into the various room types so that I can have a daily total sum
for each room type.

Linking Cells
 

Thanks BJ. I will try what you suggest and let you know. But please check in
with me next week in case we don't connect.

Have a great Memorial Day and many thanks.



"bj" wrote:

I am not sure what you mean by populate the date columns
and am not sure what you mean by the columns around the dates being merged
I will be unavailable for a couple of days around the weekend, but will
contact you again next week.
check the sumproduct equation and try putting each segement ,divided by
commas, into an sumproduct equation to try to identify from where the error
is coming.
also check sumproduct in help. Some versions of excel need semicolons
instead of commas for seperators. the #name indicates a format prolem of
some type

"KWP" wrote:

Each guest has individual in/out dates, (two columns each one showing In-Date
Out-Date) then there is a column which designates room type (there are four
different room types represented by letter: J, D, E, C). Then there are
columns for dates from 3 June to 27 Jun. Each of these columns is actually
made up of three columns which have been merged because at the bottom of the
table the information feeds into totals (here the columns are shown
individually) and show totals for the respective date as Reserved,
Contracted, Difference.

What I want to be able to do is Automatically feed from the columns which
show in and out dates to the Individual Date columns and have those populated
with the letter code for the room type.

The solution you gave me does not work. Is ther some way I can show you the
work sheet I made up? Thank you once again.









"bj" wrote:

If I understand what you want,
if your output is to be a table with room type across and date down
for example roometype indicator in B101-E101
and dates in A102-A200
in B102 enter
=sumproduct((Indate range-outdate
range),--(roomtype_range=B$101),--(indate_range<=$A102),--(outdate_range=$A102))
copy and paste to B102:E200
the --( changes the logical true false to a numeric 1 0
the arrays must be the same size but cannot reference a full column for pre
2007

"KWP" wrote:

I created a spread sheet which acts as a rooming list. Columns have names,
in date out date and room type designated by a letter (theres are 4 types).
I'm going crazy trying to set a format which will automatically prepopulate
the room type code associated with a name and the dates into individual
columns (each one for a date within the span of the time people will be in
the hotel). At the bottom of each date column I have a total (which is
segmented into the various room types so that I can have a daily total sum
for each room type.