Reference cells in named range

[Luca Brasi]

I have a large sheet with data. One of the columns is defined as a named
range (Key_x).
On another sheet, I would like to reference to some cells in that
column. Is it possible to use something like
=Key_x!5
to get the value of the 5th cell (row) in this named range?
Thanks for any hints.

 

[Guest]

Hi Luca,

You could use OFFSET, as follows.

RANHGE("KEY_X").OFFSET(nRows,0) if in the same column,

HTH,
Sharon

 

[Luca Brasi]

Sharon, thanks for your reply.

I tried
=RANGE("key_x").OFFSET(2;0)
which is your formula adjusted to my needs.

I tried as well
=OFFSET(key_a; 2; 0)
because the OFFSET formula seems to take three parameters.

But Excel keeps on telling me that the formula contains an error.
What am I missing??

BTW, using Excel XP and I don't talk about VBA.

 

[Guest]

Hi Luca,

I was meaning within VBA. Sorry for that!

If in Excel , is almost the same, give a try to:
=OFFSET(key_a, 2, 0)

Take care with colons or semicolons, depending what country configuration
you have.

HTH

 

[Luca Brasi]

I already tried different things using the OFFSET formula. But didn't
succeed. Have you really got it working??

 

[Luca Brasi]

=OFFSET(key_a, 0, 0)
would work as formula, but of course makes no sense...

 

[Guest]

Hi Luca,

Sure I've it working!

You may publish an example of what are you doing...
By the way, did you try something like
=key_a ??

(it should work the same as =OFFSET(key_a,0,0) ....


Sharon

 

[Luca Brasi]

I don't do anything more than explained in my first post. I have a
column defined as a named range. On another sheet I try to reference
specific cells (based on the row index) in that range.

I just don't see why I fail to get the value of the second row in the
named column using
=OFFSET(key_a,1,0)

I always get a REF error. What am I doing wrong?

 

[Luca Brasi]

Just "=key_a" works (as well as "=OFFSET(key_a,0,0)") but is not what I
need. I want to be able to define a different row index.

 

[Guest]

So, if I understand well:

=key_a works
=offset(key_a,0,0) works

AND

=offset(key_a,3,0) DOESN'T WORK


Am I right?

If so, my Excel knowledge doesn't go so far .....

Sharon

 

[Luca Brasi]

Correct. I now even tested it on another machine using Excel 2007. Same
behavior.

 

[Guest]

Luca,

I think I got it!

If range name involves more than one cellm, then you need

=offset(key_a,0,0,nRows,0)


Give it a try!

HTH,

Sharon

 

[Ron Rosenfeld]

I have a large sheet with data. One of the columns is defined as a named
range (Key_x).
On another sheet, I would like to reference to some cells in that
column. Is it possible to use something like
=Key_x!5
to get the value of the 5th cell (row) in this named range?
Thanks for any hints.

=INDEX(named_range,2,1)

will refer to row 2 in the named range

If named_range only has a single column, then the column argument is optional.
--ron

 

[Luca Brasi]

you rock! thank you very much, ron.

=INDEX(named_range,2,1)

will refer to row 2 in the named range

If named_range only has a single column, then the column argument is optional.
--ron

 

[Luca Brasi]

didn't do it either for me...
anyway, thanks for your time!

 

[David Heaton]

Luca,

Sharon was on the right lines with her suggestions I just think there were
some misunderstandings. The format for OFFSET is -
=OFFSET (Range, RowOffset,ColOffset, ReturnHeight,ReturnWidth)

So for your need you would use

offset(key_a,1,4,1,1)

that will return the item in col 1 and row 5 of your range..... (offset
starts counting at zero hence why 4 will return the 5th row)

Hope this helps

David

 

[Luca Brasi]

Thanks for the info.

Luca,

Sharon was on the right lines with her suggestions I just think there
were some misunderstandings. The format for OFFSET is -
=OFFSET (Range, RowOffset,ColOffset, ReturnHeight,ReturnWidth)

So for your need you would use

offset(key_a,1,4,1,1)

that will return the item in col 1 and row 5 of your range..... (offset
starts counting at zero hence why 4 will return the 5th row)

Hope this helps

David

 

[Ron Rosenfeld]

you rock! thank you very much, ron.

You're welcome. Glad to help. Thanks for the feedback.
--ron

 

[kounoike]

How about using INDEX like "=INDEX(te,5)" instead of using OFFSET ?

keizi

 

[Stan Brown]

Luca,

I think I got it!

If range name involves more than one cellm, then you need

=offset(key_a,0,0,nRows,0)

I think you mean that last parameter (number of columns) to be a 1.

For that matter, unless we're talking about an array formula I think
the penultimate parameter (number of rows) also needs to be a 1.