Dynamic Named Range 1

[binaryfingers]

Hi all

I have a worksheet that has data structured into Actual, Forecast, Variance on three consecutive lines,

eg

Line 5 Actual
Line 6 Forecast
Line 7 Variance
Line 8 Var %
Line 9 (blank)
Line 10 Actual
Line 11 Forecast
Line 12 Variance
Line 13 Var %

and this repeats itself for 100 products

Is there a way of instead of having to type in 100 named ranges to get a solution where the named ranges are automatically incremented by 5 lines?

Eg named range would follow the syle Data001, Data002, Data003 etc

I require the named ranges primarily for chart data..

Does anybody have any ideas?

Thanks,

 

[SkipVought]

Hi,

I encounter similar source data, YUCK!

However, I work with it and do dynamic charts from a Forms Combobox.

Here's my approch:

Get rid of the EMPTY rows, no matter WHAT!!!!!!!!!!!!

Create a separate sheet for Chart Data.

On the Chart Data sheet do a QUERY, via Data>Get External Data... Excel Files - YOUR WORKBOOK - the Original Data sheet as the Table - Query ALL fields Where Product equals the Selected Product from the ComboBox. This assumes that the Product Value is on each corresponding row in a column.

The Chart Source Data for each series (your list above), is static.

If you want to take this route, it will take a little bit of VBA code, so please repost in Forum707.

================================
A less desirable solution would be to create you table on the source data. It will initially look UGLY!

Add a Forms Combobox to select a Product (gotta have a list) Filter the source data on the seleted product.

AutoFilter the Source Data on the Selected Product criteria. You'll need to macro record setting the AutoFilter criteria and modify it to assign the Selected Program. That's about the only code you'll need. Assign that macro to the OnAction event of the Combox.

Good Luck, Mr. Phelps! BYW, it's NOT Impossible!


Skip,

_[red][/red]

 

[yogia]

Hi binaryfingers:

Here is one way to set the Range Names in column A starting with cell A4, using VBA ...

Sub SetRangeNamesA4A13A19()
' Macro May-25-2007 by Yogi Anand
    For i = 1 To 10
        If i < 10 Then
            yStr = "00" & i
            ElseIf i > 100 Then
            yStr = i
            Else: yStr = "0" & i
        End If
        Range("A" & (i - 1) * 5 + 4) = "Data" & yStr
        With Range("A" & (i - 1) * 5 + 4, "A" & (i * 5) + 3)
            .CreateNames Top:=True
        End With
    Next i
End Sub

I hope this helps!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

in the code in my last post , I meant to say ...

For i = 1 To [b]100[/b]

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[binaryfingers]

Hi Yogia

This is excellent, thanks,

I guess I didnt quite explain myself, sorry about that, I must get used to adding more detail.

I was after the data range to be horizontal, eg A5:L5, then A10:L10. I realise I didnt specify an ending column, as I was unsure what column that would be at the time.

I tried to modify one of the A to L, but it didnt seem to make the change, so I guess its a little more complicated than that.

Could you help me, by getting it to go horizontal, as essentially this is exactly what I was after.

thanks again,

 

[yogia]

Hi binaryfingers:

Here we go ...

Sub SetRangeNamesA4toL503_4RowsDeepEach()
' Macro May-29-2007 by Yogi Anand
    For i = 1 To 10
        If i < 10 Then
            yStr = "00" & i
            ElseIf i > 100 Then
            yStr = i
            Else: yStr = "0" & i
        End If
        ActiveWorkbook.Names.Add Name:="Data" & yStr, RefersTo:=Range(Cells(4 + (i - 1) * 5, 1)(2, 1), Cells(4 + (i - 1) * 5, 1)(5, 12))
    Next i
End Sub

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[binaryfingers]

Thanks Yogia,

A massive help!

 

[yogia]

You Are Very Welcome binaryfingers!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[binaryfingers]

Yogia

If you find time, would you mind breaking the following line down for me so that I can understand a little better the technique you are using,

ActiveWorkbook.Names.Add Name:="Data" & yStr, RefersTo:=Range(Cells(4 + (i - 1) * 5, 1)(2, 1), Cells(4 + (i - 1) * 5, 1)(5, 12))

Its great getting the answer, but it would be even better if I can fully understand it and learn from it.

I have started to work out some of the parameters, but your explanation would be great.

Thanks again,

 

[yogia]

Yogia

If you find time, would you mind breaking the following line down for me so that I can understand a little better the technique you are using,

ActiveWorkbook.Names.Add Name:="Data" & yStr, RefersTo:=Range(Cells(4 + (i - 1) * 5, 1)(2, 1), Cells(4 + (i - 1) * 5, 1)(5, 12))

Hi binaryfingers:

I have used a For Loop going from 1 to 100 (with Step of 1)

yStr is evaluated to "00" & i fro i = 1 to 9; "0" & i for i = 10 to 99; and finally i for i = 100

so that we can get the RangeName to be Data001, ... Data10, ... and Data100

The RangeName RefersTo:
Range(Cells(4 + (i - 1) * 5, 1)(2, 1), Cells(4 + (i - 1) * 5, 1)(5, 12))

which for i=1 evaluates to
Range(Cells(4,1)(2,1),Cells(4,1)(5,12))

Starting with Cell A4 ... Cells(4,1)(2,1) results in A5
and also .................... Cells(4,1)(5,12) results in L8

so Range(Cells(4,1)(2,1),Cells(4,1)(5,12)) --> Range(A5:L8)

for i=1 now we have Data001 RangeName RefersTo Range(A5:L8)

and so we continue for i=2, then 3, and all the way to 100

to have our RangeNames assigned as Data001, ... to Data100

I hope this helps!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[binaryfingers]

Thanks Yogia!