names and range names excel query

Posted by Carl Wells on July 30, 2001 12:46 AM

Hi there,
I have created various names in an excel spreadsheet, e.g. A3:A23 range has been given the name 'data_1', but I don't know how to access the data in the individual cells like in basic (e.g. data in cell 13 = data_1(13) ). There must be an easy way to do this ...

Can anyone help me?

Thanks,

Carl

Posted by Aladin Akyurek on July 30, 2001 1:08 AM

Carl,

I'm not sure about what you want. But some of the things that you can do are:

=ADDRESS(13,column(data_1)) gives you the address/ref.

=INDIRECT(ADDRESS(13,column(data_1))) gives you the value in row 13 of data_1

Aladin

===============

Posted by Carl Wells on July 30, 2001 2:54 AM

Thanks Aladin, nice and simple once you know how! This is exactly the method I was looking for, but somehow I just couldn't find it!

One slight modification, its

=INDIRECT(ADDRESS(ROW(example),13))

;)

Thanks,

Carl

Posted by Carl Wells on July 30, 2001 3:54 AM

Aladin,
I have another question for you ...
in the event that I am trying to find the address of the name in a different (and unknown) worksheet, how would I do this? Basically I am using names as if they were arrays in a normal programming language, and I don't want to hard code what sheet they come from!

Thanks again,

Carl

Posted by Carl Wells on July 30, 2001 3:54 AM

Thanks again,

Carl

Posted by Aladin Akyurek on July 30, 2001 5:07 AM

Carl,

I understood you as saying, "What is the address of a name that I defined?"

If so,

=CELL("address",Data)

where "Data" the name of a (single or multiple cell) range.

If you enter this formula in the worksheet on which it is defined, you'll simply get

$A$5

and, entered in some other sheet, you'll get

[Workbook2]Sheet1!$A$5 [ the whole path, so to say. ]

=========== ,

Posted by Carl Wells on July 30, 2001 5:20 AM

Thanks again Aladin! This is much better :).

Carl

Posted by Carl Wells on July 30, 2001 6:30 AM

Hi there Aladin,

I'm stuck again! and I think this one is really easy :/.

Basically, you gave me that I can find the absolute start addres of a name by using the CELL command

=CELL("address",LST_NAME)

Now I want to get the value of a cell from an offset of this, so I have been trying to use the OFFSET command
e.g.
=OFFSET(CELL("address",LST_NAME),0,2)

This doesn't work!

This does however (and is what te =CELL command returns by itself)

=OFFSET('Listed and unlisted holdings'!$C$3,0,2)

I have tried sticking a TEXT(CELL ...) command in case that worked but no joy :(.

god I'm really beginning to feel as if I suck :(.

What am I doing wrong?

Thanks again,

Carl

Posted by Aladin Akyurek on July 30, 2001 12:49 PM

MAKE THAT:

=OFFSET(INDIRECT(CELL("address",LST_NAME)),0,2)

Carl -- Why all this? You have something where you need such things, I suppose. What is it? Just curious. ;-)

Aladin

Posted by Carl Wells on July 31, 2001 12:52 AM

heh,

top secret ;).

Nah, basically I'm building a simple arbitrage portfolio pricing program, and I'm trying to ensure that I have an input sheet with no calculations on it, a workings sheet with no inputs on it, and an output with neither ;). And VBA is banned until my boss trusts me (and I know how to use it properly ;) ).

btw, whats the theory behind the indirect u added? as far as I can tell, its using the value (since thats what indirect does?)

/me thinks I have a lot to learn

Carl

Posted by Aladin Akyurek on July 31, 2001 11:44 AM

This is tough: I reckon you read "Help" on OFFSET & INDIRECT. The answer has eluded you, the same with me.

Lets take the following:

=OFFSET(INDIRECT("A2"),ROW()-2,0)

INDIRECT unquotes "A2" and, as a result, gives the cell ref A2 to OFFSET, an arg type the latter expects.

In our case, name has the same effect as quotes, so...

Ooh boy, should I believe what I'm stating...

me too.

Aladin