Dynamic Named Range

[scott_od]

Hi, I have an excel workbook with 2 worksheets.

The first worksheet called "Data" contains a list of places & some corresponding numbers - this data changes every month & the number of entries per location varies from month to month (for example, this month there are 5 x Rotterdam, 7 x Liverpool and 15 x Rome, however next month it could be 2 x Rotterdam, 15 x Liverpool and 8 x Rome).

[B]Excel 2012[/B][TABLE]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Place[/TD]
[TD="bgcolor: #92D050, align: center"]#'s[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Rotterdam[/TD]
[TD="align: center"]21[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Rotterdam[/TD]
[TD="align: center"]27[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Rotterdam[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Rotterdam[/TD]
[TD="align: center"]27[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Rotterdam[/TD]
[TD="align: center"]28[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Liverpool[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Liverpool[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Liverpool[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]Liverpool[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]Liverpool[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]Liverpool[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]Liverpool[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]Rome[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]Rome[/TD]
[TD="align: center"]18[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]Rome[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]Rome[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD]Rome[/TD]
[TD="align: center"]18[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD]Rome[/TD]
[TD="align: center"]18[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD]Rome[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD]Rome[/TD]
[TD="align: center"]18[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD]Rome[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD]Rome[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD]Rome[/TD]
[TD="align: center"]18[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD]Rome[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD]Rome[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD]Rome[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD]Rome[/TD]
[TD="align: center"]22[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Data[/B][/COLOR][/CENTER]

The second worksheet is called "Liverpool" and contains a graph with the corresponding numbers for Liverpool.
It also includes some formulas that quickly show me which rows/data range are related to Liverpool, as this varies every month.

For the sake of transparency I have removed the "=" from the formulas so you can see how they are calculated, but the result of the formula in cell I4 is "Data!B7:Data!B13" which is the range for data related to Liverpool which is needed for the graph.

[B]Excel 2012[/B][TABLE]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #D6DCE4"]Liverpool[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]MATCH(H1,Data!$A$1:Data!$A$412,0)[/TD]
[TD]"Data!B"&CONCATENATE(H2)[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]MAX(ROW(Data!$1:$412)*(Data!$A$1:$A$412=H1))[/TD]
[TD]":Data!B"&CONCATENATE(H3)[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD]CONCATENATE(I2,I3)[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Liverpool[/B][/COLOR][/CENTER]

*note the formula in H3 is an array formula.

What I would like to do is use the value in cell I4 as the range for the graph. I believe I cannot do this directly, but must create a named range, but I am also struggling to make this work.

Can anyone help?

 

[Andrew Poulsom]

Because your data is sorted you can use dynamic names like:

LiverpoolX =OFFSET(Data!$A$1,MATCH(Liverpool!$H$1,Data!$A:$A,0)-1,0,COUNTIF(Data!$A:$A,Liverpool!$H$1))
LiverpoolY =OFFSET(LiverpoolX,0,1)

 

[scott_od]

Andrew thanks so much, I really appreciate that.
It works fine - I don't full understand it yet, but it works

One more question: What if this was the data set & I wanted separate named ranges for Liverpool via London and Liverpool via St Albans

[RANGE=cls:xl2bb-100][XR][XH=cs:4]Excel 2012[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][/XR][XR][XH]1[/XH][XD=h:l|fw:b]Place[/XD][XD=h:l|fw:b]Via[/XD][XD=h:c|fw:b|bc:92d050|c:ffffff]#'s[/XD][/XR][XR][XH]2[/XH][XD=h:l]Rotterdam[/XD][XD=h:l]Den Haag[/XD][XD=h:c]21[/XD][/XR][XR][XH]3[/XH][XD=h:l]Rotterdam[/XD][XD=h:l]Den Haag[/XD][XD=h:c]27[/XD][/XR][XR][XH]4[/XH][XD=h:l]Rotterdam[/XD][XD=h:l]Den Haag[/XD][XD=h:c]26[/XD][/XR][XR][XH]5[/XH][XD=h:l]Rotterdam[/XD][XD=h:l]Den Haag[/XD][XD=h:c]27[/XD][/XR][XR][XH]6[/XH][XD=h:l]Rotterdam[/XD][XD=h:l]Den Haag[/XD][XD=h:c]28[/XD][/XR][XR][XH]7[/XH][XD=h:l|c:ff0000]Liverpool[/XD][XD=h:l|c:ff0000]London[/XD][XD=h:c|c:ff0000]15[/XD][/XR][XR][XH]8[/XH][XD=h:l|c:ff0000]Liverpool[/XD][XD=h:l|c:ff0000]London[/XD][XD=h:c|c:ff0000]26[/XD][/XR][XR][XH]9[/XH][XD=h:l|c:ff0000]Liverpool[/XD][XD=h:l|c:ff0000]London[/XD][XD=h:c|c:ff0000]26[/XD][/XR][XR][XH]10[/XH][XD=h:l|c:ff0000]Liverpool[/XD][XD=h:l|c:ff0000]St Albans[/XD][XD=h:c|c:ff0000]26[/XD][/XR][XR][XH]11[/XH][XD=h:l|c:ff0000]Liverpool[/XD][XD=h:l|c:ff0000]St Albans[/XD][XD=h:c|c:ff0000]26[/XD][/XR][XR][XH]12[/XH][XD=h:l|c:ff0000]Liverpool[/XD][XD=h:l|c:ff0000]St Albans[/XD][XD=h:c|c:ff0000]26[/XD][/XR][XR][XH]13[/XH][XD=h:l|c:ff0000]Liverpool[/XD][XD=h:l|c:ff0000]St Albans[/XD][XD=h:c|c:ff0000]30[/XD][/XR][XR][XH]14[/XH][XD=h:l|c:ff0000]Liverpool[/XD][XD=h:l|c:ff0000]St Albans[/XD][XD=h:c|c:ff0000]7[/XD][/XR][XR][XH]15[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]20[/XD][/XR][XR][XH]16[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]18[/XD][/XR][XR][XH]17[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]22[/XD][/XR][XR][XH]18[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]22[/XD][/XR][XR][XH]19[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]18[/XD][/XR][XR][XH]20[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]18[/XD][/XR][XR][XH]21[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]20[/XD][/XR][XR][XH]22[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]18[/XD][/XR][XR][XH]23[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]20[/XD][/XR][XR][XH]24[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]20[/XD][/XR][XR][XH]25[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]18[/XD][/XR][XR][XH]26[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]22[/XD][/XR][XR][XH]27[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]22[/XD][/XR][XR][XH]28[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]22[/XD][/XR][XR][XH]29[/XH][XD=h:l]Rome[/XD][XD=h:l]Milan[/XD][XD=h:c]22[/XD][/XR][XR][XH=cs:4][RANGE][XR][XD]Data[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[Andrew Poulsom]

With the Via location in H2:

=OFFSET(Data!$A$1,MATCH(1,INDEX((Data!$A:$A=Liverpool!$H$1)*(Data!$B:$B=Liverpool!$H$2),),0)-1,0,COUNTIFS(Data!$A:$A,Liverpool!$H$1,Data!$B:$B,Liverpool!$H$2))

 

[scott_od]

Thanks again Andrew