Had a wrong email address posted...please see above for
correct...thank you...

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

Hi
use INDIRECT. e.g.
MATCH(1, INDIRECT("'Worksheet A'!" & X1 & ":" & X1), 0)

where X1 stores your row number


--
Regards
Frank Kabel
Frankfurt, Germany

"Andrew Scurrah" schrieb im Newsbeitrag
...
Had a wrong email address posted...please see above for
correct...thank you...

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

Andrew responded that use of indirect works for the problem. This was to
his previous posting of the question.

--
Regards,
Tom Ogilvy

"Frank Kabel" wrote in message
...
Hi
use INDIRECT. e.g.
MATCH(1, INDIRECT("'Worksheet A'!" & X1 & ":" & X1), 0)

where X1 stores your row number


--
Regards
Frank Kabel
Frankfurt, Germany

"Andrew Scurrah" schrieb im Newsbeitrag
...
Had a wrong email address posted...please see above for
correct...thank you...

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

Hi Tom
haven't seen that post. Thanks for the info

Frank

--
Regards
Frank Kabel
Frankfurt, Germany

"Tom Ogilvy" schrieb im Newsbeitrag
...
Andrew responded that use of indirect works for the problem. This
was to
his previous posting of the question.

--
Regards,
Tom Ogilvy

"Frank Kabel" wrote in message
...
Hi
use INDIRECT. e.g.
MATCH(1, INDIRECT("'Worksheet A'!" & X1 & ":" & X1), 0)

where X1 stores your row number


--
Regards
Frank Kabel
Frankfurt, Germany

"Andrew Scurrah" schrieb im Newsbeitrag
...
Had a wrong email address posted...please see above for
correct...thank you...

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