I wrote a 6,000-line macro but am getting stuck with the following problem.
(I posted this in "General Questions" but I didn't receive any responses so I
thought I'd try "Programming.")

I need to determine a price based 735 combinations of
choices. Basically, in Column A, I have a list of 7 days of arrival
(Mon-Sun). In Column B, I have a list of 15 length-of-hours worked (from 8
hours over 1 day to 80 hours over 12 days); in Column C, I have a list of 7
days of departure (Mon-Sun). (I created the lists using Data, Validation,
List). The User chooses one from each
column in ANY combination (7 x 15 x 7) ... that's 735 combinations. I can
(try
to) figure out how an OLAP cube or a Pivot Table works ... but I don't want
to waste my time if it's not going to solve my problem to begin with. So, do
you know if I can create some kind of cube/table/something so that I
determine a price, in Column D, based on any combination of the three lists?
Thanks a lot.

"crimsonkng" wrote in message
...
I wrote a 6,000-line macro but am getting stuck with the following problem.
(I posted this in "General Questions" but I didn't receive any responses
so I
thought I'd try "Programming.")

I need to determine a price based 735 combinations of
choices. Basically, in Column A, I have a list of 7 days of arrival
(Mon-Sun). In Column B, I have a list of 15 length-of-hours worked (from
8
hours over 1 day to 80 hours over 12 days); in Column C, I have a list of
7
days of departure (Mon-Sun). (I created the lists using Data, Validation,
List). The User chooses one from each
column in ANY combination (7 x 15 x 7) ... that's 735 combinations. I can
(try
to) figure out how an OLAP cube or a Pivot Table works ... but I don't
want
to waste my time if it's not going to solve my problem to begin with. So,
do
you know if I can create some kind of cube/table/something so that I
determine a price, in Column D, based on any combination of the three
lists?

Is there a mathematical relationship betweeen the different combinations or
do you want to define them all manually?

Hmmmm, I could define them all manually, I suppose, but that seems like an
awful lot of work ... and I'd probably miss some of the combinations. But if
it makes it easier in the long run, then I could define them manually, I
guess.

But, I can assign a value to each of the individual choices, too. For
example, one of the combinations is: Arrive Sunday ($395) and Work For 40
hours over 5 Days ($4,000) and depart on Saturday ($395). So, that total
would be $4,790.

Thanks for your help, Damien.

"Damien McBain" wrote:

"crimsonkng" wrote in message
...
I wrote a 6,000-line macro but am getting stuck with the following problem.
(I posted this in "General Questions" but I didn't receive any responses
so I
thought I'd try "Programming.")

I need to determine a price based 735 combinations of
choices. Basically, in Column A, I have a list of 7 days of arrival
(Mon-Sun). In Column B, I have a list of 15 length-of-hours worked (from
8
hours over 1 day to 80 hours over 12 days); in Column C, I have a list of
7
days of departure (Mon-Sun). (I created the lists using Data, Validation,
List). The User chooses one from each
column in ANY combination (7 x 15 x 7) ... that's 735 combinations. I can
(try
to) figure out how an OLAP cube or a Pivot Table works ... but I don't
want
to waste my time if it's not going to solve my problem to begin with. So,
do
you know if I can create some kind of cube/table/something so that I
determine a price, in Column D, based on any combination of the three
lists?

Is there a mathematical relationship betweeen the different combinations or
do you want to define them all manually?

Actually ... let me clarify that ... the prices are not static ... sometimes,
the price is dependant upon the combination of the other two choices. That's
why I was thinking of a type of "cube" configuration.

"crimsonkng" wrote:

Hmmmm, I could define them all manually, I suppose, but that seems like an
awful lot of work ... and I'd probably miss some of the combinations. But if
it makes it easier in the long run, then I could define them manually, I
guess.

But, I can assign a value to each of the individual choices, too. For
example, one of the combinations is: Arrive Sunday ($395) and Work For 40
hours over 5 Days ($4,000) and depart on Saturday ($395). So, that total
would be $4,790.

Thanks for your help, Damien.

"Damien McBain" wrote:

"crimsonkng" wrote in message
...
I wrote a 6,000-line macro but am getting stuck with the following problem.
(I posted this in "General Questions" but I didn't receive any responses
so I
thought I'd try "Programming.")

I need to determine a price based 735 combinations of
choices. Basically, in Column A, I have a list of 7 days of arrival
(Mon-Sun). In Column B, I have a list of 15 length-of-hours worked (from
8
hours over 1 day to 80 hours over 12 days); in Column C, I have a list of
7
days of departure (Mon-Sun). (I created the lists using Data, Validation,
List). The User chooses one from each
column in ANY combination (7 x 15 x 7) ... that's 735 combinations. I can
(try
to) figure out how an OLAP cube or a Pivot Table works ... but I don't
want
to waste my time if it's not going to solve my problem to begin with. So,
do
you know if I can create some kind of cube/table/something so that I
determine a price, in Column D, based on any combination of the three
lists?

Is there a mathematical relationship betweeen the different combinations or
do you want to define them all manually?

"crimsonkng" wrote in message
...
Actually ... let me clarify that ... the prices are not static ...
sometimes,
the price is dependant upon the combination of the other two choices.
That's
why I was thinking of a type of "cube" configuration.

"crimsonkng" wrote:

Hmmmm, I could define them all manually, I suppose, but that seems like
an
awful lot of work ... and I'd probably miss some of the combinations.
But if
it makes it easier in the long run, then I could define them manually, I
guess.

But, I can assign a value to each of the individual choices, too. For
example, one of the combinations is: Arrive Sunday ($395) and Work For
40
hours over 5 Days ($4,000) and depart on Saturday ($395). So, that total
would be $4,790.

Thanks for your help, Damien.

How about just a mapping table that assigns values to the text strings like
String Value
Start Sun $400
End Mon $0
Work 48 $3000
etc

Then you can just use a worksheet formula to get the value like (the name
maptable is the mapping table):
=vlookup(a2,maptable,2,false)+vlookup(b2,maptable, 2,false)+vlookup(c2,maptable,2,false)

Or is say "arrive sunday" a different value depending on the contents of the
other 2 related cells in the same row?


"Damien McBain" wrote:

"crimsonkng" wrote in message
...
I wrote a 6,000-line macro but am getting stuck with the following
problem.
(I posted this in "General Questions" but I didn't receive any
responses
so I
thought I'd try "Programming.")

I need to determine a price based 735 combinations of
choices. Basically, in Column A, I have a list of 7 days of arrival
(Mon-Sun). In Column B, I have a list of 15 length-of-hours worked
(from
8
hours over 1 day to 80 hours over 12 days); in Column C, I have a
list of
7
days of departure (Mon-Sun). (I created the lists using Data,
Validation,
List). The User chooses one from each
column in ANY combination (7 x 15 x 7) ... that's 735 combinations.
I can
(try
to) figure out how an OLAP cube or a Pivot Table works ... but I
don't
want
to waste my time if it's not going to solve my problem to begin with.
So,
do
you know if I can create some kind of cube/table/something so that I
determine a price, in Column D, based on any combination of the three
lists?

Is there a mathematical relationship betweeen the different
combinations or
do you want to define them all manually?

Thanks, Damien, for the suggestions. I'm afraid that it's getting a little
beyond my capabilities, though. I'll just program it with a sledgehammer
instead of a paint brush ... I'll figure it out somehow. Thanks, again. Dan

"Damien McBain" wrote:

"crimsonkng" wrote in message
...
Actually ... let me clarify that ... the prices are not static ...
sometimes,
the price is dependant upon the combination of the other two choices.
That's
why I was thinking of a type of "cube" configuration.

"crimsonkng" wrote:

Hmmmm, I could define them all manually, I suppose, but that seems like
an
awful lot of work ... and I'd probably miss some of the combinations.
But if
it makes it easier in the long run, then I could define them manually, I
guess.

But, I can assign a value to each of the individual choices, too. For
example, one of the combinations is: Arrive Sunday ($395) and Work For
40
hours over 5 Days ($4,000) and depart on Saturday ($395). So, that total
would be $4,790.

Thanks for your help, Damien.

How about just a mapping table that assigns values to the text strings like
String Value
Start Sun $400
End Mon $0
Work 48 $3000
etc

Then you can just use a worksheet formula to get the value like (the name
maptable is the mapping table):
=vlookup(a2,maptable,2,false)+vlookup(b2,maptable, 2,false)+vlookup(c2,maptable,2,false)

Or is say "arrive sunday" a different value depending on the contents of the
other 2 related cells in the same row?


"Damien McBain" wrote:

"crimsonkng" wrote in message
...
I wrote a 6,000-line macro but am getting stuck with the following
problem.
(I posted this in "General Questions" but I didn't receive any
responses
so I
thought I'd try "Programming.")

I need to determine a price based 735 combinations of
choices. Basically, in Column A, I have a list of 7 days of arrival
(Mon-Sun). In Column B, I have a list of 15 length-of-hours worked
(from
8
hours over 1 day to 80 hours over 12 days); in Column C, I have a
list of
7
days of departure (Mon-Sun). (I created the lists using Data,
Validation,
List). The User chooses one from each
column in ANY combination (7 x 15 x 7) ... that's 735 combinations.
I can
(try
to) figure out how an OLAP cube or a Pivot Table works ... but I
don't
want
to waste my time if it's not going to solve my problem to begin with.
So,
do
you know if I can create some kind of cube/table/something so that I
determine a price, in Column D, based on any combination of the three
lists?

Is there a mathematical relationship betweeen the different
combinations or
do you want to define them all manually?