Named range not continuous. How to display values?

[Fries]

Hi all,

I have a worksheet with names on it. Every name is positioned above a
list.
I created a Name with the following not continuous range: sheet1!$B
$5,sheet1!$G$5,sheet1!$L$5 etc.

Now I want to use this name on another sheet to make a vertical
listing of all the names in this range. I´ve tried it with array
formulas, the INDEX function... but I cannot seem to get it right.

anyone???

thank you

Fries

 

[Gary''s Student]

Suppose the Name is:
whatsina
First enter this UDF:

Function element(r As Range, i As Integer) As Variant
j = 1
For Each rr In r
element = rr.Value
If j = i Then Exit Function
j = j + 1
Next
End Function

This UDF will walk across a range (continuous or not). Then, elsewhere in
any sheet in the workbook, enter:

=element(whatsina,1)
=element(whatsina,2)
=element(whatsina,3)

 

[Max]

Don't think this is possible. Why not just frame it up directly in a
continuous vert/horiz range using simple link formulas?

---
Hi all,

I have a worksheet with names on it. Every name is positioned above a
list.
I created a Name with the following not continuous range: sheet1!$B
$5,sheet1!$G$5,sheet1!$L$5 etc.

Now I want to use this name on another sheet to make a vertical
listing of all the names in this range. I´ve tried it with array
formulas, the INDEX function... but I cannot seem to get it right.

anyone???

thank you

Fries

 

[T. Valko]

Try this:

Assume you enter the first formula in cell A1:

=INDEX(Sheet1!B$5:IV$5,ROWS(A$1:A1)*5-4)

Copy down as needed.

Will return:

=Sheet1!B5
=Sheet1!G5
=Sheet1!L5
=Sheet1!Q5
etc
etc

--
Biff
Microsoft Excel MVP


Hi all,

I have a worksheet with names on it. Every name is positioned above a
list.
I created a Name with the following not continuous range: sheet1!$B
$5,sheet1!$G$5,sheet1!$L$5 etc.

Now I want to use this name on another sheet to make a vertical
listing of all the names in this range. I´ve tried it with array
formulas, the INDEX function... but I cannot seem to get it right.

anyone???

thank you

Fries

 

[Fries]

Thanks Gary´s student,

this did the trick.

Also thanks to the rest for your answers.

Fries