Named range not continuous. How to display values?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fries - 26 May 2008 10:42 GMT

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

Reply to this Message

Gary''s Student - 26 May 2008 16:20 GMT

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)

Signature

Gary''s Student - gsnu2007i

> Hi all,
>
[quoted text clipped - 12 lines]
>
> Fries

Reply to this Message

Fries - 29 May 2008 11:41 GMT

Thanks Gary´s student,

this did the trick.

Also thanks to the rest for your answers.

Fries

On 26 mayo, 17:20, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:

> Suppose the Name is:
> whatsina
[quoted text clipped - 37 lines]
>
> - Mostrar texto de la cita -

Reply to this Message

Max - 26 May 2008 16:23 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

T. Valko - 26 May 2008 18:05 GMT

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

Signature

Biff
Microsoft Excel MVP

Reply to this Message