Excel - How to duplicate a chart that relies on dynamic named ranges
Asked By Mike C
21-Apr-08 01:44 AM
Hello - I have created a dynamic chart that shows the most recent 12
months of data.
So, for example, one of my series value sets is based on the defined
range:
=OFFSET('Dynamic Chart'!XValues,0,3). And "Xvalues" is a defined
range that uses the Counta function to show the most recent 12 months
of data.
The chart works well. But the problem is that I don't know how to
duplicate the chart into other worksheets. Is there a way to transfer
the defined names AND the series from a chart so that I can duplicate
such a chart in other worksheets and in new workbooks.
When I simply copy and paste the chart into a new spreadsheet, or when
I copy the sheet (via Right click > "Move or copy"), the chart does
not read the series correctly.
I guess one options is for me to repeatedly resave the workbook as a
new name. But I would imagine that there has got to be a way to copy
a chart (and the corresponding named ranges and series), so that it
will function the same way in a new spreadsheet or workbook.
Any suggestions are appreciated....
Microsoft Excel
(1)
Chart
(1)
Workbook
(1)
OFFSET
(1)
XValues
(1)
Counta
(1)
Resave
(1)
Months
(1)
Shane Devenshire replied...
Hi Mike,
Open both files and tile them. In the file with the defined names, hold
down the Ctrl key and drag the sheet with the chart to the other book. When
you copy any sheet from one workbook to another all names in the original
workbook will come along. You may need to modify some of the names if they
reference the original workbook. You can do that quickly with the Edit,
Links command.
Cheers,
Shane Devenshire
Microsoft Excel MVP
Mike C replied...
en
hey
Great. Thanks SHane!
amortissement Excel bonsoir, quelqu'un pourrait il m'aider = E0 trouver une fa = E7on plus ad = E9quate E9cembre '* ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * = * ** * '_ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ _ Boucle _ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ __ _ '* ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * = * ** * Range("A10").Select For i = 3D 1 To Duree + 1 ActiveCell.Offset(i).Interior.ColorIndex = 3D 8 ActiveCell.Offset(i, 0) = 3D i 'on d = E9finit le nombre de rang soit le nombre de pour g = E9rer le prorata de la premi = E8re ann = E9e d'amortissement If ActiveCell.Offset(i, 0) = 3D 1 Then ActiveCell.Offset(i, 1) = 3D annee ActiveCell.Offset(i, 2) = 3D DateDebut & " au 31 / 12 / " & annee ActiveCell.Offset(i, 3) = 3D PrixAcquisitionHT ActiveCell.Offset(i, 4) = 3D PrixAcquisitionHT * Taux * (30 + 1 - Day(DateDebut) + 30 * (12 - Month(DateDebut))) / 360 'annuit
Win7 VirtualStore Weirdness with Excel 2002 and earlier Excel I thought I'd share a weird experience I had today in case it saves installer for it - - which registers it as an add-in for all installed versions of Excel - - and tested it. Fine in Windows 2000 in all installed versions of Excel. Boot to Windows XP, fine there too. Boot to Windows 7. Fine in Excel 2010, 2007, and 2003. But Excel 2002 and 2000 were still loading the old version of the add-in. I uninstalled the add-in, made sure it was gone, and reinstalled it. Same problem in Excel 2002 and earlier; still works fine in Excel 2003 and later. I opened the VB editor in Excel 2003 and in Excel 2002
A workbook of macros which runs on external workbooks. Excel Hello! You guys made up this handy macro which I put in one workbook (Tester.xls). It opens, checks sheet and workbook protection status, and closes an external workbook (Tested.xls) based on a dialogue box. Pretty cool little code. (My sheet names were result As String Dim i As Integer Dim Count As Integer NewFN = Application.GetOpenFilename(FileFilter: = "Excel Files (*.xls), *.xls", Title: = "Please select a file") If NewFN = False Then ' They pressed Cancel Then X = True If ActiveWorkbook.ProtectStructure Then X = True If X = False Then MsgBox "The workbook is not protected." Else MsgBox "The workbook is protected." End If oldbk.Close savechanges: = False End Sub Anyway, Your group also made
Aggiornamento - Microsoft Risponde all’evoluzione delle Community Excel Cosa accade? Per informarvi che Microsoft comincerà presto a chiudere I newsgroup e a “transitare”, pian piano, se lo vorrete, il traffico sui Forum Microsoft. Perchè? Come ben sapete, i newsgroup sono esistiti per molti anni; il problema però è newsgroup. E’ possibile trovare informazioni su come installare e configurare il bridge qui: http: / / connect.microsoft.com / MicrosoftForums / Perchè i newsgroup verranno chiusi? Tutti i newsgroup pubblici verranno chiusi tra il la chiusura dei newsgroup, dove posso andare online per continuare le discussioni e ricevere supporto? Microsoft vi fornisce un’ampia scelta di forum, alcuni dei quali coprono lo stesso argomento od ottimale per continuare le discussioni online. Raccomandiamo quindi di cominciare con il http: / / social.answers.microsoft.com / Forums / it-IT / officeexcelit forum Vi invitiamo comunque a visitare i Forum Microsoft: http: / / www.microsoft.com / communities / forums / default.mspx Se ho domande, chi devo contattare? Se
Update Chart Source Data Excel I used the "Record Macro" function to get the code shown below. When I recorded Select Selection.Copy Sheets("Chart1").Select ActiveChart.PlotArea.Select Application.CutCopyMode = False ActiveChart.SeriesCollection(1).XValues = " = 'Approval Index'! R2C1:R392C1" ActiveChart.SeriesCollection(1).Values = " = 'Approval Index'! R2C5:R392C5" ActiveChart.SeriesCollection(2).XValues = " = 'Approval Index'! R2C1:R392C1" ActiveChart.SeriesCollection(2).Values = " = 'Approval Index'! R2C7:R392C7" Sheets("Data Input Select Range("A13").Select Thanks for your help. . . Excel Programming Discussions ActiveChart.ChartArea.Select (1) ActiveChart.PlotArea.Select (1) SeriesCollection (1) ChartArea (1) PlotArea Looks like your X / category values are in 'Approval Index'!A1:A# Series1-Y values offset in col-E Series2-Y values offset in Col-G where # is the value in A1. Define the following names, myCat = OFFSET('Approval Index'!$A$1, 1, 0, 'Approval Index'!$A$1-1) mySeries1 = OFFSET(myCat, , 4