How to dynamically reference a dynamic named range
 

I did a search, but came up empty.

Assume I have a dynamic named range called "RANGE" that starts at A1.
As I add to A2, A3, the named range increases. That's the easy part.

Now I want to have cells elsewhere on the sheet refer to the contents
of the named range.

Say I want to have L1 display A1, L2 display A2, etc. In effect, I
want the L column to mirror A. How can I get this to happen
dynamically, so that if I have three items in RANGE, then the new cell
range is three cells tall, etc.... and if I add to column A, it
automatically is reflected in column L.

I suspect I have to use OFFSET with the named range, but I'm lost as to
how to apply that logic.

Thanks in advance.


--
paris3
------------------------------------------------------------------------
paris3's Profile: http://www.excelforum.com/member.php...fo&userid=3542
View this thread: http://www.excelforum.com/showthread...hreadid=381412

right click on your tab, select view code and paste this into the module.
replace "NamedRange" with whatever your range name is.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("NamedRange")) Is Nothing Then _
Range("NamedRange").Copy Range("L1")

End Sub



"paris3" wrote:


I did a search, but came up empty.

Assume I have a dynamic named range called "RANGE" that starts at A1.
As I add to A2, A3, the named range increases. That's the easy part.

Now I want to have cells elsewhere on the sheet refer to the contents
of the named range.

Say I want to have L1 display A1, L2 display A2, etc. In effect, I
want the L column to mirror A. How can I get this to happen
dynamically, so that if I have three items in RANGE, then the new cell
range is three cells tall, etc.... and if I add to column A, it
automatically is reflected in column L.

I suspect I have to use OFFSET with the named range, but I'm lost as to
how to apply that logic.

Thanks in advance.


--
paris3
------------------------------------------------------------------------
paris3's Profile: http://www.excelforum.com/member.php...fo&userid=3542
View this thread: http://www.excelforum.com/showthread...hreadid=381412

Thanks.. but... (and I don't mean to be an ingrate)...

The solution has to be code-free, relying only on formulas. This is a
for a variety of reasons.


--
paris3
------------------------------------------------------------------------
paris3's Profile: http://www.excelforum.com/member.php...fo&userid=3542
View this thread: http://www.excelforum.com/showthread...hreadid=381412

Try...

L1, copied down:

=IF(ROWS($L$1:L1)<=COUNTA(RANGE),INDEX(RANGE,ROWS( $L$1:L1)),"")

Hope this helps!

paris3 Wrote:
I did a search, but came up empty.

Assume I have a dynamic named range called "RANGE" that starts at A1.
As I add to A2, A3, the named range increases. That's the easy part.

Now I want to have cells elsewhere on the sheet refer to the contents
of the named range.

Say I want to have L1 display A1, L2 display A2, etc. In effect, I
want the L column to mirror A. How can I get this to happen
dynamically, so that if I have three items in RANGE, then the new cell
range is three cells tall, etc.... and if I add to column A, it
automatically is reflected in column L.

I suspect I have to use OFFSET with the named range, but I'm lost as to
how to apply that logic.

Thanks in advance.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=381412

In L1 type =A1, then copy down column L. You will have to copy enough
formulas in column L to accomodate the size of your named range.


"paris3" wrote:


Thanks.. but... (and I don't mean to be an ingrate)...

The solution has to be code-free, relying only on formulas. This is a
for a variety of reasons.


--
paris3
------------------------------------------------------------------------
paris3's Profile: http://www.excelforum.com/member.php...fo&userid=3542
View this thread: http://www.excelforum.com/showthread...hreadid=381412