Accessing Name ranges

[WildAngus]

Hi,

Can anyone help me with this one....

I'm playing around with name ranges in excel and I'm finding it very useful for a task I'm trying to do, but I'm hitting a problem that I can't seem to find a solution for.

If I name a range of 20 items in a list starting in cell A6 for instance, and I want to reproduce that list of names on a worksheet and have them start in cell C2, I just get a #VALUE! returned. If I drag this formula down a number of rows I get the first name appearing in C6 (i.e. the equivalent row to the named list starting position).

I've tried a few obvious ideas (eg suffix with '-4') but can't work it out and can't seem to find anything in the help section.

Any ideas anyone?

John

 

[Yard]

The technique you are trying to use only works for ranges which are either horizontally or vertically parallel. This is why the result is correct for C6 onwards.

Try something like

=INDEX(MyRange,ROW()-ROW($2:$2)+1)

 

[WildAngus]

OK - Thanks

This seems to work. I'll just do a bit of reading up to understand how the parameters work.

Cheers for your help.