nXn matrix creation

[AG]

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

 

[GS]

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.

 

[AG]

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

 

[GS]

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?

 

[Bernd P]

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

 

[AG]

Hi Gary,

Firstly, thanks for spending time on this and replying to me. I think
giving a sample spreadsheet is the best option but I am not sure how
to attach a file here on these posts. Can I send it directly to you?
Please let me know. Thanks again.

Regards,
AG

 

[AG]

Hi Bernd,

I am not sure if I understood your question.

Regards,
AG

 

[GS]

AG wrote on 6/4/2010 :

Hi Gary,

Firstly, thanks for spending time on this and replying to me. I think
giving a sample spreadsheet is the best option but I am not sure how
to attach a file here on these posts. Can I send it directly to you?
Please let me know. Thanks again.

Regards,
AG

You need to be using a newsreader (like Outlook Express or something)
to be able to send attachments. You can send a sample to me if:

You use the same title as this thread
The sample contains the expected results (done manually)

My mailbox is:
gesansom at netscape dot net