Named range not continuous. How to display values?

  • Thread starter Thread starter Fries
  • Start date Start date
F

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
 
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)
 
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
 
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
 
Thanks Gary´s student,

this did the trick.

Also thanks to the rest for your answers.

Fries
 
Back
Top