On 3 Jun., 15:32, AG 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