• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Graphs with Named Ranges

S

sam77

New Member
Hi,


I have another problem here. There is a file with dyanmic graphs with named ranges which im using. However when i move/copy that worksheet into another excel file, the graph does not work. I've checked the named ranges everything remains the same however the graph just does not work. (There is not VBA code involved)Can anyone help please!!


Regards,

Sam
 
If your named ranges are in place, have you checked that the graph isn't referring back to the original sheet?


Right-click the graph, choose "Select Data", and then "Edit" for one of your graph lines. Make sure that the lines (or whatever series you're graphing) are pointed to the current instance of the named ranges--the ones in your current workbook.


Good luck!
 
First, check the named range (Insert - Name - Define), make sure the formulas are still good.

Second, check the chart. Named ranges in charts have to use the workbook name, so I'm guessing that's the more likely source of problem.

E.g.,

='My workbooks.xls'!MyRange
 
I think my problem is similar. I have 8 graphs on separate tabs referring to (what I would like to be the named ranges) ranges on a Subtotal tab. These ranges do change from time to time; I adjust them manually and that is fine. My problem is that the graphs refuse to keep the reference as named ranges. It will accept the named ranges when I enter them in the source data box, but as soon as I OK and go back they are converted to fixed references. Such as ='Subtotals Paste Here'!$D$14:$E$22. This range is named District1, but gets converted in the graph source data box to the fixed reference. Subtotals Paste Here is the tab name of the data table.


What am I missing???


Thanks for any / all help.


Deb
 
@Deb

When you put Named Formula into the Reference box for the series use the sheet name as well as the name

eg: ='Subtotals Paste Here'!District1

Don't worry, when Excel converts it back to a formula like ='My workbooks.xls'!District1


@Sam

Goto Data, Edit Links

find the link to the old workbook, change it and reference it to the current workbook, this will upgrade all these links in one pass.
 
Hi,

I am using one of your fantastic tips in one of my excel worksheets; There is a file with dynamic graphs (like a dashboard) with named ranges which I’m using, with multiple VBA codes similar to the example below:

Sub SelectCustom()

[Cname] = [FCustK]

End Sub

Also there are some index formulas and dropdown selection boxes in the charts.

So here it’s my problem. When I chose one of the items in the dropdown selection I’ve got the following warning message: No items of this name exist in the Pivot table report. Rename “Selection” to “Previous selection”

I have checked the detail in the pivot table in case there is no data, but there are some figures.


Thanks a lot for your help in advance,

Kind regards,

Leonardo
 
@lirguio


In the future, please start a new thread with your own question. Tacking on your own question to someone else's thread is called "thread hijacking" and is generally frowned upon. As your question/problem contains several different aspects, it might be helpful to upload a workbook so we can see what all is going on. Perhaps you could do that, and then post a link (in a new thread) for us, along with your question restated?


Thanks.
 
Hi Luke M.

Thanks for your quick answer and suggestion, I will try to recreate the problem and come back with the information, Thanks a lot and apologize for the mistake I’ve made.

Regards,


Leonardo
 
Hi,


I am new to this VBA project. I have an issue with my current project. any help by today EOD is appreciated.


The Issue.


I have an excel file which has sheets as "Report", "OverallProgram"...

In the "Report" sheet I have a chart(graph) which should show the graph of data from the sheet "OverallProgram" I have 45 rows of column in the "OverallProgram" sheet, but the graph in "Report" sheet shows only upto the 25 rows.


The code is

Public Sub OverallProgram_TestScript()

On Error Resume Next


x_TDconnect

Set Sh = ThisWorkbook.Sheets("OverallProgram")

'Set sh1 = ThisWorkbook.Sheets("Credentials")


ReleaseNames = "'" & Selected_App & "'"


Query = "select planned_date, re_forcasting, sum(executed) over (partition by rel_id order by planned_date ROWS BETWEEN UNBOUNDED PRECEDING AND current row) executed,Passed from (select rel_id, to_char(tc_plan_scheduling_date,'MM/DD/YYYY') planned_date, sum(case when tc_exec_date > tc_plan_scheduling_date then 1 else 0 end) Re_forcasting, sum(case when tc_status in ('Passed', 'Failed') then 1 else null end ) Executed, sum(case when tc_status in ('Passed') then 1 else 0 end) Passed from TESTCYCL tcl, releases rls, RELEASE_CYCLES rcl where tcl.TC_ASSIGN_RCYC= rcl.RCYC_ID and rcl.rcyc_parent_id=rls.rel_id and tc_plan_scheduling_date is not null and rls.rel_name = " & ReleaseNames & " group by to_char(tc_plan_scheduling_date,'MM/DD/YYYY') , rel_id ) order by planned_date"


Set comExecute = TDConnection.Command

comExecute.CommandText = Query

Set RecSet = comExecute.Execute

COUNT1 = RecSet.RecordCount


PrevCount = Sh.Cells(1, 1)

Sh.Cells(1, 1) = COUNT1


Sh.Range("B3:F" & PrevCount).Select

Selection.ClearContents


l = 3

m = 2

Reforecast = 0

Passed = 0


If COUNT1 > 0 Then

Sh.Range("B3:F" & l + COUNT1 - 1).Select

Selection.ClearContents

Sh.Range("B3:F" & l + COUNT1 - 1).Value = "0"


For k = 1 To COUNT1

For j = 0 To 10

If j = 0 Then

Sh.Cells(l, m) = RecSet(j)

Sh.Cells(l, m + 1) = ""

Else

Sh.Cells(l, m + 1) = RecSet(j)

If (m = 3) Then

Reforecast = Reforecast + RecSet(j)

Sh.Cells(l, m + 1) = Reforecast

ElseIf (m = 5) Then

Passed = Passed + RecSet(j)

Sh.Cells(l, m + 1) = Passed

End If

End If

m = m + 1

Next

l = l + 1

RecSet.Next

m = 2

Next

End If


'MsgBox "OverallProgramTestScript Report Generated Successfully !!! Please refer to Reports Sheet for Complete Report!!!", vbInformation, "Reports Generated"


TDConnection.Disconnect


TDConnection.ReleaseConnection

Set TDConnection = Nothing


ActiveSheet.ChartObjects("OverallProgramTestScript").Activate


ActiveChart.SetSourceData Source:=Sheets("OverallProgram").Range("C2:F" & COUNT1 + 3)

ActiveChart.SeriesCollection(1).XValues = "='OverallProgram'!$B$3:$F$ " & COUNT1 + 3

Cells.Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

Selection.Borders(xlEdgeLeft).LineStyle = xlNone

Selection.Borders(xlEdgeTop).LineStyle = xlNone

Selection.Borders(xlEdgeBottom).LineStyle = xlNone

Selection.Borders(xlEdgeRight).LineStyle = xlNone

Selection.Borders(xlInsideVertical).LineStyle = xlNone

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Range("B2:F" & COUNT1 + 3).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlInsideHorizontal)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

Range("B2:F2").Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Range("B2:F" & COUNT1 + 3).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlMedium

End With

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

Err_Handler:

On Error Resume Next

TDConnection.Disconnect

TDConnection.Logout

TDConnection.ReleaseConnection

Set TDConnection = Nothing

On Error GoTo 0


End Sub


Query is working fine. only thing is the data in the graph is not showing upto the end.

could anybody help me with this issue its very urgent.
 
You must log in or register to reply here.
Back
Top