Create Dynamic Named Ranges in Excel Across Rows Going Up/Down

Hey Guys, I am trying to create a dynamic chart in VBA. I have defined a bunch of ranges in the excel workbook which correspond a dynamic range. The user is prompted with a userform, and then based on his decisions I want to make a graph. The userform will turn cells either "True" or "False", and if a call is true, I want to add a corresponding named range which represents a dynamic range. So long story short, I create an array of strings which corresponds to the way excel would handle the dynamic range with charting (i.e. graphARR(1)='==Workbook x'!Range_1 and so on and so on, up to 10 entries in graphARR). Later, when graphARR is full of the strings which represent the dynamic ranges I want, i use: VB:

For icolumn = 1 To graphARR.count 
    With ActiveChart.SeriesCollection.NewSeries 
        .Values = graphARR(icolumn) 
        .XValues = rngchtxval 
        .name = rngchtdata(1, icolumn) 
    End With 
Next 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



However, there is an error when it tries to call graphARR(icolumn). I know there are values in graphARR because when I put in a messagebox higher up in the code, it will return the value (i.e. "==Workbook x'!Range_1"

Does anyone know how to get this to work, or some sort of work around? Thank you very much for all your help!

I figured out the way I would name each of the elements in my chart. For example, the axis values are defined thusly:

=OFFSET('Quarterly Results'!$C$8,100-COUNTBLANK('Quarterly Results'!$C8:$C107)-'Quarterly Results'!$R$7,0,'Quarterly Results'!$R$7,1)

Where data can exist in the 100 cells from C8:C107, I am using a countblank because some cells have formulas that return "" under certain conditions, and R7 is a user controlled number of results to include in the chart. I can use similar definitions for the elements I wish to display in the chart.

My goal is for the user to select a number of quarters to display by entering a number between 1 and 100 in cell R7, and the chart would expand its definitions to match that input.

Is this possible? I did it with Spark Lines, and now I want to do it for my charts, too.

Good day Guys , I am new to here and seeking great help from you guys, I need a macro that , when i toggle the macro, it will go to every column xldown and redefine the column name using the top name like alpha beta and carrot, if i input another info in the column it will auto redefine it to group it to the same column name....

alpha | Beta | Carrot
asd |asd | asd
asd |asd | asd
asd |asd |
asd | |

i want to be when i input new data to the existing column, it will select until the xldown and redefine the column name by using the top name....

Hope to get the answer from you guyz soon...

Hi guys,

Have been a regular visitor for some time, picking up some great tips and knowledge in my Excel endeavours; however I have a problem where I'm requiring some assistance. Hoping someone here can help?

I'm attempting to write a code that will set dynamic named ranges across rows using the name in column A (starting at A2, A3, A4 etc) as the defined name and continuing down an undefined number of rows until a blank is found.

I've previous written a similiar code to set dynamic named ranges for columns (below) that works well, but no luck so far.

Code:

Sheets("Agent Shifts").Select
 
        Dim wb As Workbook, ws As Worksheet
        Dim lrow As Long, lcol As Long, i As Long
        Dim myName As String, Start As String
 
        Const Rowno = 1
 
        Const Offset = 1
 
        Const Colno = 1
        Set wb = ActiveWorkbook
        Set ws = ActiveSheet
 
        lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
        lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
        Start = Cells(Rowno, Colno).Address
 
            wb.Names.Add Name:="lcol", _
                 RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
            wb.Names.Add Name:="lrow", _
                 RefersToR1C1:="=COUNTA(C" & Colno & ")"
            wb.Names.Add Name:="myData", RefersTo:= _
                  "=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
        For i = Colno To lcol
 
        myName = Replace(Cells(Rowno, i).Value, " ", "_")
        If myName = "" Then
 
            MsgBox "Fatal Error " & i & vbCrLf _
                   & "Please contact #### to repair code"
            Exit Sub
        End If
 
        wb.Names.Add Name:=myName, RefersToR1C1:= _
             "=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
nexti:
        Next i

Is anyone able to help or point me in the right direction?

Cheers.

Hello everyone,
This is my first post.Hope I make sense.
Here's an attached sample file which will clearly indicate what I require.
I just found out that same name cannot represent two cells in a workbook.
Sheet1 contains the format which is copied to sheet2 in the way as shown.
Now Sheet1 has names defined for various cells like Potato for cell containing Potato and so on.
You will find that sheet2 has name Title_potato name for cell contaning potato.
This Title_potato and other names in sheet2 I want to generate with a vba code when contents are copied from sheet1 from sheet2.
I hope now it is clear.
any help will be greatly appreciated.

2 part question, I'm befuddled on this one:

I'm importing data from a SQL server. Since this same query will be used / updated several times, I'd like to make it a bit more dynamic. Here are the two things I'd like to be able to do:

1) Dynamic ranges: Pull data from a determined number of days; formula-wise, I'm looking for data '>=today()-7' . Can't seem to figure out how to do this with SQL.

2) Named ranges: Be able to build a query that reads from a named range in Excel, and uses that as criteria.

Please Help! I will be teaching a beginner's course over SQL/Excel soon, and the capability to do the above would be wonderful not only for myself, but for the more inclined people in the class .

Thanks in advance for any suggestions
- Thomas

I have a spreadsheet with a large number of graphs on and lots of data.

Most the graphs look across 20 or so columns and one or 2 rows down.

The problem is that l need to insert new colums in now, and obviously excel graphs dont recognise the inserted column data.

Therefore l believe using named ranges may solve this problem...

I have the formula =OFFSET(Sheet1!$A$1,0,0,2,MATCH("DC",Sheet1!$1:$1))

However i need to expand on this to achieve what i need to do.

1. Firstly l only want to look from column C onwards (so ignoring all data in column A and B)
2. I need the match to match to columns starting with the letters "DC", however there is a number after this, so it is not a direct match. The match needs to ignore columns with any other heading start.
3. is there anyway to automatically refresh the graphs to the named range once it changes, or any simble vb macro i can run?


I hope this makes some sence, anyhelp would be trully appreciated!

Many thanks in advance,

Matt

How do I create a dynamic named range in Excel for Mac 2011? I can't figure out how to get to a dialog that allows me to enter a formula for a range that I've already created.

Hello everyone,

I am trying to create a process which will take my data and convert it into a graph. I plan to use macro so that I can convert my data into graph everyday, instantly, with out having to go through the whole process like I do right now.

So I was able to create a macro which converts data into graph. However, this macro only works for file 'Book1'. I think I know what the problem is.
To graph, I am using named ranges to create dynamic charts. ( Please see http://office.microsoft.com/en-us/ex...098011033.aspx ). Now in my source data of my graph, the value formula "Sheet1!time" changes to "Book1!time". Now the problem is that if I run this macro in a new file it doesn't work.

The following line gives me an error. As you can see, "Book1" is the problem. Is there a way I could change "Book1" to "Sheet1" ?

Windows("Book1").SmallScroll Down:=18 'error on this line
ActiveSheet.Shapes("Chart 1").IncrementLeft -32.25
ActiveSheet.Shapes("Chart 1").IncrementTop 331.5
Windows("Sheet1").SmallScroll Down:=42
ActiveSheet.Shapes("Chart 1").IncrementLeft -60.75
ActiveSheet.Shapes("Chart 1").IncrementTop 544.5
Windows("Sheet1").SmallScroll Down:=27
ActiveSheet.Shapes("Chart 1").IncrementLeft -45.75
ActiveSheet.Shapes("Chart 1").IncrementTop 375.75
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.44, msoFalse,

Thanks,

Hello

I m trying to make a query based on a dynamic range and paste the results in a sheettab of my wrorkbook. How can I do it?

Thanks once again