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.

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.

Try...

L1, copied down:

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

Hope this helps!

Originally Posted by paris3

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.

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>

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
>
>