Hi,

I remember that a fancy and tidy solution to my problem exists - but I've
forgotten it. Can you help please?

All my named ranges here utilise offset formulas: they can change shape,
size and they can move.

I have a named range spanning multiple columns and rows. Now I want to run a
match formula on the 5th column of this named range. Rather than creating a
new named range, how can I tidily reference the 5th column of my existing
named range?

Also, I have another named range just covering 1 row. How, by referencing
this named range, can I pick out the value in the 7th column?

Thanks ever so much... I'll go and see if I can return a favour to somebody
else here =o)

Basil

Got it.

=INDEX(NAMED_RANGE,,5)

Where 5 is the column number desired.

Sorry to have troubled you.

B

"Basil" wrote:

Hi,

I remember that a fancy and tidy solution to my problem exists - but I've
forgotten it. Can you help please?

All my named ranges here utilise offset formulas: they can change shape,
size and they can move.

I have a named range spanning multiple columns and rows. Now I want to run a
match formula on the 5th column of this named range. Rather than creating a
new named range, how can I tidily reference the 5th column of my existing
named range?

Also, I have another named range just covering 1 row. How, by referencing
this named range, can I pick out the value in the 7th column?

Thanks ever so much... I'll go and see if I can return a favour to somebody
else here =o)

Basil

5th column of range "MyRange"....

=MATCH(H1,INDEX(MyRange,,5),0)


Single row ....7th column

=INDEX(TopRow,,7)

HTH



"Basil" wrote:

Hi,

I remember that a fancy and tidy solution to my problem exists - but I've
forgotten it. Can you help please?

All my named ranges here utilise offset formulas: they can change shape,
size and they can move.

I have a named range spanning multiple columns and rows. Now I want to run a
match formula on the 5th column of this named range. Rather than creating a
new named range, how can I tidily reference the 5th column of my existing
named range?

Also, I have another named range just covering 1 row. How, by referencing
this named range, can I pick out the value in the 7th column?

Thanks ever so much... I'll go and see if I can return a favour to somebody
else here =o)

Basil