Hi There,

I wonder if anybody knows how to change a row reference
dynamically within an argument string...

I have the following example of a row array lookup from
one sheet to another looking at column values that I want
to make dynamic so it can apply to ANY row in a
worksheet...

Main sheet is called "Working", and the lookup sheet is
called "Worksheet A"

eg.
MATCH(1, 'Worksheet A'!42:42, 0)

I want to reference the row dynamically, ie. get rid of
the 42:42 row reference and replace with a generic column
lookup based on changing row which will be consecutive in
sequence (they look at a pivot table).

eg.
MATCH(1, 'Worksheet A'!XX:XX, 0)

Where I can change the "XX:XX" dynamically, with each
iteration of the lookup (I will be incrementing and
changing the reference, ie. 42:42, then 43:43, then 81:81,
then 82:82, then 83:83, etc...

I know this sounds tricky, but I wonder if anyone can help.

Thanks in advance.

Andrew

This is a worksheet formula?

=MATCH(1, Indirect("'Worksheet A'!" & B9 & ":" & B9), 0)

Where B9 is a cell containing the number 42 or the number 83.

--
Regards,
Tom Ogilvy

"Andrew Scurrah" wrote in message
...
Hi There,

I wonder if anybody knows how to change a row reference
dynamically within an argument string...

I have the following example of a row array lookup from
one sheet to another looking at column values that I want
to make dynamic so it can apply to ANY row in a
worksheet...

Main sheet is called "Working", and the lookup sheet is
called "Worksheet A"

eg.
MATCH(1, 'Worksheet A'!42:42, 0)

I want to reference the row dynamically, ie. get rid of
the 42:42 row reference and replace with a generic column
lookup based on changing row which will be consecutive in
sequence (they look at a pivot table).

eg.
MATCH(1, 'Worksheet A'!XX:XX, 0)

Where I can change the "XX:XX" dynamically, with each
iteration of the lookup (I will be incrementing and
changing the reference, ie. 42:42, then 43:43, then 81:81,
then 82:82, then 83:83, etc...

I know this sounds tricky, but I wonder if anyone can help.

Thanks in advance.

Andrew

Thanks...that totally works !!!

Andrew
-----Original Message-----
This is a worksheet formula?

=MATCH(1, Indirect("'Worksheet A'!" & B9 & ":" & B9), 0)

Where B9 is a cell containing the number 42 or the number
83.

--
Regards,
Tom Ogilvy

"Andrew Scurrah" wrote in
message
...
Hi There,

I wonder if anybody knows how to change a row reference
dynamically within an argument string...

I have the following example of a row array lookup from
one sheet to another looking at column values that I
want
to make dynamic so it can apply to ANY row in a
worksheet...

Main sheet is called "Working", and the lookup sheet is
called "Worksheet A"

eg.
MATCH(1, 'Worksheet A'!42:42, 0)

I want to reference the row dynamically, ie. get rid of
the 42:42 row reference and replace with a generic
column
lookup based on changing row which will be consecutive
in
sequence (they look at a pivot table).

eg.
MATCH(1, 'Worksheet A'!XX:XX, 0)

Where I can change the "XX:XX" dynamically, with each
iteration of the lookup (I will be incrementing and
changing the reference, ie. 42:42, then 43:43, then
81:81,
then 82:82, then 83:83, etc...

I know this sounds tricky, but I wonder if anyone can
help.

Thanks in advance.

Andrew


.

Sorry...email address was wrong...

please send replies to "
-----Original Message-----
Hi There,

I wonder if anybody knows how to change a row reference
dynamically within an argument string...

I have the following example of a row array lookup from
one sheet to another looking at column values that I want
to make dynamic so it can apply to ANY row in a
worksheet...

Main sheet is called "Working", and the lookup sheet is
called "Worksheet A"

eg.
MATCH(1, 'Worksheet A'!42:42, 0)

I want to reference the row dynamically, ie. get rid of
the 42:42 row reference and replace with a generic column
lookup based on changing row which will be consecutive in
sequence (they look at a pivot table).

eg.
MATCH(1, 'Worksheet A'!XX:XX, 0)

Where I can change the "XX:XX" dynamically, with each
iteration of the lookup (I will be incrementing and
changing the reference, ie. 42:42, then 43:43, then
81:81,
then 82:82, then 83:83, etc...

I know this sounds tricky, but I wonder if anyone can
help.

Thanks in advance.

Andrew
.