Hi,

can anyone tell me if it is possible to make a formula that gives an item of
a named range which contains more than one cell?

eg. I have a named range called "Result" and I want to have the third result
in a cell. It should be something like:

=Result.cells(3) but cells does not exist in Excel, only in VBA

Thanks
Jos Vens

Jos,

Have a look at the Index function.

e.g. Index(Test,3) returns the cell in the 3rd row of a vertical range.

Robin Hammond
www.enhanceddatasystems.com

"Jos Vens" wrote in message
...
Hi,

can anyone tell me if it is possible to make a formula that gives an item
of
a named range which contains more than one cell?

eg. I have a named range called "Result" and I want to have the third
result
in a cell. It should be something like:

=Result.cells(3) but cells does not exist in Excel, only in VBA

Thanks
Jos Vens

Thanks Robin,

this is exactly what I'm looking for! It saves me creating a lot of named
cells!

Jos

"Robin Hammond" schreef in bericht
...
Jos,

Have a look at the Index function.

e.g. Index(Test,3) returns the cell in the 3rd row of a vertical range.

Robin Hammond
www.enhanceddatasystems.com

"Jos Vens" wrote in message
...
Hi,

can anyone tell me if it is possible to make a formula that gives an
item
of
a named range which contains more than one cell?

eg. I have a named range called "Result" and I want to have the third
result
in a cell. It should be something like:

=Result.cells(3) but cells does not exist in Excel, only in VBA

Thanks
Jos Vens

Note: if the range has 2 dimensions..

=index($a$10:$g$40,3) will return an error.

=index($a$10:$g$40,3,0) returns an array of values in 3rd row
=index($a$10:$g$40,0,3) returns an array of values in 3rd column

=index($a$10:$g$40,3,1) will return 1 itm =3rd row,1st col



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Jos Vens" wrote:

Thanks Robin,

this is exactly what I'm looking for! It saves me creating a lot of
named cells!

Jos

"Robin Hammond" schreef in bericht
...
Jos,

Have a look at the Index function.

e.g. Index(Test,3) returns the cell in the 3rd row of a vertical
range.

Robin Hammond
www.enhanceddatasystems.com

"Jos Vens" wrote in message
...
Hi,

can anyone tell me if it is possible to make a formula that gives
an item of a named range which contains more than one cell?

eg. I have a named range called "Result" and I want to have the
third result in a cell. It should be something like:

=Result.cells(3) but cells does not exist in Excel, only in VBA

Thanks
Jos Vens

Thanks for the extra information!

Jos Vens

"keepITcool" schreef in bericht
...

Note: if the range has 2 dimensions..

=index($a$10:$g$40,3) will return an error.

=index($a$10:$g$40,3,0) returns an array of values in 3rd row
=index($a$10:$g$40,0,3) returns an array of values in 3rd column

=index($a$10:$g$40,3,1) will return 1 itm =3rd row,1st col



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Jos Vens" wrote:

Thanks Robin,

this is exactly what I'm looking for! It saves me creating a lot of
named cells!

Jos

"Robin Hammond" schreef in bericht
...
Jos,

Have a look at the Index function.

e.g. Index(Test,3) returns the cell in the 3rd row of a vertical
range.

Robin Hammond
www.enhanceddatasystems.com

"Jos Vens" wrote in message
...
Hi,

can anyone tell me if it is possible to make a formula that gives
an item of a named range which contains more than one cell?

eg. I have a named range called "Result" and I want to have the
third result in a cell. It should be something like:

=Result.cells(3) but cells does not exist in Excel, only in VBA

Thanks
Jos Vens