Newbie!

[Suze]

Hi all, I am new to the forum and Excel.
I am trying to set up a two sheets, one that references data in the other. I got the concept of names, I have some columns of stuff in my data sheet and when I name cells or ranges I can put in "=name" on the first sheet and, like magic, the data appears. But that name applies to a row not a single cell. How do I reference the individual items in the row? For example.....

name description age
fred big 23
jim small 25
bob medium 29

What I want to do is define each row as the name of the person and then reference each item of data in the other sheet. Assuming that I have named the rows fred, jim and bob, then to reference the name in the first sheet I just put in "=jim" ( which returns jim) but how do I reference the rest of the data? ie, how do I get jims description? I thought I migh have to define labels description and age, but I can't get that to work.

[MattShoreson]

use the formula INDEX.

Use:

=index(range,row,column)

so for example...

highlight all the data and call it 'people'

=index(people,row(jim),column(age))

however, a point of note if you range doesnt start A1, you will need to compensate for this...

i.e. if your range is A10:c12 then you would need....

=index(people,row(jim)-9,column(age))

[Suze]

Uh! I think I have it working. Thanks! In particular, the bit about the -9. Although I can't exactly fathom why, mine needs a -8 ( despite the fact that is looks like it should need a -7) and without your help there I don't think I would ever have guessed that it needed a "-" anything! :-)

[MattShoreson]

My example needed a -9 because

index works by using a area, the first column being A and the first row being 1, no matter where you are on the sheet.

So if the top left cell is not A1, you have to force excel to think it is.

So in the example the area is a10:c12, the columns are ok a age equates to column c. But a10 is 9 rows in addition to a1. take 9 off the value to force excel into thinking the range starts at a1!