Prev: How to deliver an Excel tool training
Next: Copy and paste to other worksheets
From: AG on 3 Jun 2010 10:32
Hi All,

I have a need to create a matrix based on some user choices. The
requirement is described below:

User Inputs 5 options Opt1, Opt2, Opt3, Opt4, Opt5. Then runs the
macro to generate the matrix. The matrix should look like below

Opt1 Opt2 Opt3
Opt4 Opt5
Opt1 1
Opt2 choice21 1
Opt3 choice31 choice32 1
Opt4 choice41 choice42 choice43 1
Opt5 choice51 choice52 choice53 choice54
1

The choices is the drop down menu created using Data > Validation >
List. In this matrix user will make certain choices. The cells above
the diagonal need to have a formula, which is the reciprocal of the
choice for a corresponding combination below the diagonal, that is, if
intersection of RowValue = Opt2 and ColumnValue = Opt1 is choice21
then intersection of RowValue = Opt1 and ColumnValue = Opt2 will be a
formula = 1/choice21.

The number of options a user can provide could be anything it could be
as small as 2 and as large as 20-30 options. So the matrix need to be
sized accordingly.

I am totally clueless on this. Any help on this will me much
appreciated. Thanks in advance.

Regards,
AG
From: GS on 3 Jun 2010 12:22
AG formulated the question :
> Hi All,
>
> I have a need to create a matrix based on some user choices. The
> requirement is described below:
>
> User Inputs 5 options Opt1, Opt2, Opt3, Opt4, Opt5. Then runs the
> macro to generate the matrix. The matrix should look like below
>
> Opt1 Opt2 Opt3
> Opt4 Opt5
> Opt1 1
> Opt2 choice21 1
> Opt3 choice31 choice32 1
> Opt4 choice41 choice42 choice43 1
> Opt5 choice51 choice52 choice53 choice54
> 1
>
> The choices is the drop down menu created using Data > Validation >
> List. In this matrix user will make certain choices. The cells above
> the diagonal need to have a formula, which is the reciprocal of the
> choice for a corresponding combination below the diagonal, that is, if
> intersection of RowValue = Opt2 and ColumnValue = Opt1 is choice21
> then intersection of RowValue = Opt1 and ColumnValue = Opt2 will be a
> formula = 1/choice21.
>
> The number of options a user can provide could be anything it could be
> as small as 2 and as large as 20-30 options. So the matrix need to be
> sized accordingly.
>
> I am totally clueless on this. Any help on this will me much
> appreciated. Thanks in advance.
>
> Regards,
> AG

The example you gave for the construction of the formula is easy enough
to do since it only involves 1 choice. How do you want to construct the
formula for the remaining rows below Rows(Opt2)? I can assume it would
include all choices in the respective row, but I'd rather you
explicitly state exactly what results you want.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


From: AG on 3 Jun 2010 18:23
Hi Garry,

The choices given to the users below the diagonal is a drop down with
5 options - numbers 1, 2, 3, 4, 5. And the users will choose one of
the those choices. So, once they have made the choice, say for Row
(Opt3) Col (Opt2) = choice32 (lets assume the user chooses 5) then the
formula I need in the cell at the intersection of Row (Opt2) Col
(Opt3) is 1/choice32 (or 1/5).

I am not sure if that was sufficient info. I could create something in
a spreadsheet and send it over if that helps.

Regards,
AG

From: GS on 3 Jun 2010 22:39
AG expressed precisely :
> Hi Garry,
>
> The choices given to the users below the diagonal is a drop down with
> 5 options - numbers 1, 2, 3, 4, 5. And the users will choose one of
> the those choices. So, once they have made the choice, say for Row
> (Opt3) Col (Opt2) = choice32 (lets assume the user chooses 5) then the
> formula I need in the cell at the intersection of Row (Opt2) Col
> (Opt3) is 1/choice32 (or 1/5).
>
> I am not sure if that was sufficient info. I could create something in
> a spreadsheet and send it over if that helps.
>
> Regards,
> AG

Hi AG,
Your reply covers what you want if the user picks choice32 in
Col(Opt2), but my Q was what do you want when they also pick choice31
in Col(Opt1)?

The issue lies in that the cells below the diagonal are filled, whereas
the cells above are blank except where you want the formula.

Also, what purpose does the cells with '1' in them serve in this?

Perhaps you should attach a spreadsheet to your reply, that shows
examples of how this matrix needs to be constructed and demonstrate
where/what goes in the formula cell.

This sounds like a spreadsheet design issue more than a VBA solution
issue. Are you open to either/both?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


From: Bernd P on 4 Jun 2010 02:21
On 3 Jun., 15:32, AG <amol...(a)gmail.com> wrote:
> Hi All,
>
> I have a need to create a matrix based on some user choices. The
> requirement is described below:
>
> User Inputs 5 options Opt1, Opt2, Opt3, Opt4, Opt5. Then runs the
> macro to generate the matrix. The matrix should look like below
>
>                     Opt1          Opt2          Opt3
> Opt4          Opt5
> Opt1               1
> Opt2           choice21         1
> Opt3           choice31      choice32         1
> Opt4           choice41      choice42    choice43        1
> Opt5           choice51      choice52    choice53     choice54
> 1
>
> The choices is the drop down menu created using Data > Validation >
> List. In this matrix user will make certain choices. The cells above
> the diagonal need to have a formula, which is the reciprocal of the
> choice for a corresponding combination below the diagonal, that is, if
> intersection of RowValue = Opt2 and ColumnValue = Opt1 is choice21
> then intersection of RowValue = Opt1 and ColumnValue = Opt2 will be a
> formula = 1/choice21.
>
> The number of options a user can provide could be anything it could be
> as small as 2 and as large as 20-30 options. So the matrix need to be
> sized accordingly.
>
> I am totally clueless on this. Any help on this will me much
> appreciated. Thanks in advance.
>
> Regards,
> AG

Hello,

Normally I would like to do this with a macro. If you need to use
worksheet functions only I would search to the left for the diagonal
"1", then downwards for the other diagonal "1" and according to their
position look up the corresponding element. This search needs to be a
bit tricky because you do not want to find a "1" which is part the of
data, not the diagonal. Hmm, actually I think I would prefer an unused
character in the diagonal which cannot be part of the data (the tricky
bit would otherwise need something like a name HasFormula with Excel4
macro commands, I guess).

Regards,
Bernd
 |  Next  |  Last
Pages: 1 2
Prev: How to deliver an Excel tool training
Next: Copy and paste to other worksheets