Naming a range versus formula in Excel

[Jos Vens]

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

 

[Robin Hammond]

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]

Thanks Robin,

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

Jos

 

[keepITcool]

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]

Thanks for the extra information!

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 >