Jon, the first 4 sheets in my workbook have about 40 charts each. How are the labels applied? End If How can the formula in A2 pick up the 12 and adjust (A1:G1) to (A1:L1) so that the whole formula now reads =SUM(A1:L1)/A4 ? Personal The one after running the initial diagnostics. When you want to make changes to an existing formula, select the formula cell and press F2 to enter the Edit mode.Once you do this, the cursor starts flashing at the end of the closing parenthesis in the cell or formula bar (depending on whether the Allow editing directly in cells option is checked or . I just tested in Excel 365 (equivalent to Excel 2019) and the code executed as expected, and all charts on all worksheets were changed. the reference will be there only on another PC or in the future) without a update data dialog for every cell Dominick =MAX(Sheet1!$B$2:$D$8) The Find/Replace functionality works on filename, sheet name, row numbers and column letters, as long as you are careful when entering your strings. Im sure Im doing something wrong but can;t figure out what! I used to use the ChangeSeriesFormulaAllCharts macro for Excel 2003 and it worked brilliantly. I understand. See, even if I was to include only numerical values, the charts still have floating point issues. Clicking on the charts series will reveal something that looks like this: =SERIES(test,,{0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29},1). I dont know whats not working. Under Legend Entries (Series), click Edit.. Or otherwise to allow a workbookhaving 46 chart sheets that plot data from 23 columns of a spreadsheet two different waysto be used for a different spreadsheet? created by copy as pictures)? Type a value in the next cell to establish a pattern. Select cells A1:B4. Thanks! [A1], instead use ws.Range("A1"). I agree with your point and the easiest way to solve my problem is to link the chart data to hidden sheets; however, as I mentioned before, that would involve a ton of code to be rewritten. I cant thank you enough! Insertar una serie en Excel. | EXCEL FORO: Un blog de Excel Now, in some of that spare time you have, I would like drop downs of the sheet names prefilled, ideally with the change to box prefilled with the current sheet, and the from box filled with sheet information from the series. I wonder whether reinstalling Excel 2013 would fix the problem. I can the use your add in to update the charts to point to the current sheet but the pictures remain pointing to the original sheet. However, recently I wanted to replace series formulas (chart copies pasted on another computer with different paths, so I could not open those originals) by new formulas: The Edit Series Formulas feature works like Find and Replace, but for your charts' series formulas. The problem with partially empty chart series formulas (see my post above: long paths with commas results in missing y-data) may be solved in some way by changing worksheet Links instead. I had no idea it was possible, cant believe it worked so well. When I have updated the graphs with the correct source string, the graph graphics update, however the data values dont i.e. What I was thinking was needed was an ability to do a find and replace on the source link for each linked picture on the page just like you do for charts? Understanding data series | Exceljet Where can I send a donation Seriously? Im glad there is a different approach, do you mind my asking what it was? Making the charts pictures would normally be the best choice; however I need the charts to remain scalable for use in other reports or applications like PowerPoint. The bad news is I still do not understand why this particular Excel does not want to access anything related to graphs in general (and it is not a personal version as you might guess so I will take it as unchangeable). Marko It seem to be hung up on three charts and Im not sure what is different about these charts that is causing the problem. In the format data labels options, there is an option under label contains that allows value from cells You can also perform these next steps directly in the Formula bar. What Im doing is comparable to: Plot, by month, yielding one graph line, The behavior you describe is not normal. Basically what Im trying to do is change all of the series with CZ to DA. I dont have 2013 on this computer, so I cant test it. i would prefer vba code rather than an add in. Thanks, Jon tried that too. What kind of chart are you having problems with? But there's more to it To make it easier to create the double-sided badges, you can use the "&" operator to concatenate the first name and last name columns from Sheet 2 into one column. As you can see the only change is the !MA1.2PersonalInsurance becomes !MA1.2CommercialInsurance which is the correct named range. Excel charts: add title, customize chart axis, legend and - Ablebits Saved hours of very tedious work. Dim newCF As String What do you think? I was excited I bumped into this, but I still get the Unknown Error Type Mismatched. It returns a formula in R1C1 notation, or the text of a label if there is no link. Working now, thx. as does this, =2011!$B$8:$M$8,2010!$B$8:$M$8,2009!$B$8:$M$8. The ChangeSeriesFormulaAllCharts routine in Change Series Formulas only works on all chart objects embedded in one sheet. strTemp = WorksheetFunction.Substitute(mySrs.Formula, _ This kind of thing happens when Names fail in charts. ?WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString) Things that could change: data file source folder; filename; spreadsheet name; number of rows of data; titles of plotted columns (thus, of plots). I am currently working on graphs where your little add in would come very handy. When you copy a chart, dont you have a regular Paste option? End Sub, Thanks Kenneth (https://peltiertech.com/change-series-formula-improved-routines/#comment-135418). Microsoft knows about this bug and a fix is on the way. that is very useful! For i = 1 To nlinks F2 key in Excel - edit formulas. you could use InStr to see whether the series formula contains the find string. Keep Formatting While Filling a Series in Excel 5. I tested with a similar formula and it worked fine. Using fully patched Excel 2003. This is how all links from chart to worksheet have to be specified. or again, if I want an 8-day SMA, the formula will change to =SUM(A1:H1)/A4, etc. 2. Im trying to simulate an oscilloscope using an Excel Chart. Two of the charts (column charts with two series) use data on the Pivot Table Report Worksheet itself and these are no problem at all. These were problematic in Excel 2003 and earlier but are fixed in these versions. Your code was perfect to enable updating all of the data series in each chart on each sheet, but sometimes (like on sheets 2, 3 and 4 but not on sheet 1) the plot areas get moved so the charts end up jumbled together. I currently have the A2:B101 format, and it gives me the one XY series I I was just trying to increase my VBA skills a little. Both the filename and the range name Datecode need changing. You just have to specify your strings thoughtfully. (Its not too hard to write some VBA to help you edit series formulas, but thats not todays [], Your email address will not be published. I am attempting to change x and y values location from a global scope (NEW ASR DATABASE.xls) to a worksheet scope (CF-DATA). Thanks. The checkbox tells you the file is open, but the lack of buttons on the Add-Ins tab indicates that it is not. 1. Has anyone else run into this? For example, I can merge ten ranges like this: =SERIES(Sheet1!$B$1,(Sheet1!$A$2:$A$35,Sheet1!$C$2:$C$35,Sheet1!$E$2:$E$35,Sheet1!$G$2:$G$35,Sheet1!$I$2:$I$35, And it did work on some of graphs, but not most. Contact Jon at Peltier Tech to discuss training at your facility, or visit Peltier Tech Advanced Trainingfor information about public classes. But you may move your sheet (or copy chart) temporarily to a new empty worksheet, change links and move/copy back to the original location. oldMsg = Replace LINKS & vbCrLf & vbCrLf & _ Hi, Dim X, Y On a good note, I used your trick of changing markers using any shapes. Skip Rows to Fill a Series in Excel 7. I am facing the issue in excel 2007 I save and close the file. Using Excel 2003. I never tested this program with bubble charts, so I dont even know how strange their behavior is. Try this: Copyright 2023 All rights reserved. This is exactly what I have been looking for! Ive got some pretty basic VBA skills, so if youve come across something like this and can point me to it, that would be great. For i = 1 To UBound(aLinks) The glitches have to do with irregularities in how VBA reads the series formulas. CAUTION!!! It is possible to see what the link formulas are in a data label, using the ancient Excel 4 Macro (XLM) language, if you want to do a little coding of your own. For example if the number is 1 and the data series is in column A the range of the chart should be A1:A100. Hi, This utility is a great idea unfortunately I get Unknown Error Type Mismatch when I use it with Excel 2007, I have a Scatter plot with 3 series keep up the good work! The code fails if there is a chart that does not have ( {0, ). Syntax Example If you need to create a quick sample dataset, here's an example using SEQUENCE with TEXT, DATE, YEAR, and TODAY to create a dynamic list of months for a header row, where the underlying date will always be the current year. Set Excel chart data series range dynamically by a cell's value I tried to change Sp500 to none. Now that I reread your comments, I get what the problem is, missing arguments. aaLink = Replace(aLinks(i), oldPath, newPath, 1, 1, vbTextCompare) The chart shows column B plotted, but we want to plot column C instead. and into A4 I put a cell, heavily bordered, in which I enter a value to represent a number of days. Hi, I am not very familiar with Excel and trying to learn. I wrote:- In the pop-up menu, choose Series (the last item). You saved me about 12 hours worth of work. En primer lugar seleccionaremos si deseamos que nuestra serie se proyecte en . This is typical behavior of a copied sheet that contains names (also called named ranges). The box in front of the add-in Change Series Formula Add-In is listed and it is checked. I am trying to copy charts from one sheet to another. Once everything is set up to my satisfaction, I need to make six or seven sheets which use the same charts and pictures but on different individual data sets. Can I concatenate small sets of pairs? You can then see whether the score is above / below the average. Exit Sub Regarding Kens comment: I am getting the same error. These clients come from small and large organizations, in manufacturing, finance, and other areas. The find and replace for changing the workbook doesnt work for menot holding my jaw right is my guess. Youve made a gorgeous chart of the data in Sheet1. Then recreate the charts from scratch? Debug.Print UBound(aLinks); LinkTypeName xlExcelLinks When I go back and look at the Values in SourceData/Series, it is still Sp500. Then I realized your formula was longer, so I made my sheet names 31 characters long, and the program crashed. Prior to SP2, there were many strange problems with Excel 2007 charts. But these are things I dont do much myself, so my motivation is limited. Open your Excel Sheet/chart that you want to rename Right-click the chart On the menu displayed, click Select Data. Tried it in Excel 2007 and 2010. Instead of typing or autofilling. Steps Define a dynamic named range =OFFSET (Sheet1!$A$2,,,1,COUNTA (Sheet1!$A$2:$Z$2)) and give it a name mobileRange Right Click on Chart Click on Select Data This screen will come Click on Edit under Legend Entries. Use defined names to automatically update a chart range - Office This will shave hours off my updates for my monthly reports! The formula first multiplies 2 by 3, and then adds 5 to the result. Thank you :). Debug.Print oldCF Next ch Ill look into it. It looks like a binary/decimal rounding issue. Application defined or object defined error What were the original and new Names of the chart data ranges? I have looked at the XY Chart Labels utility and this doesnt give you the option of creating a link so that doesnt help. Error message : run time error 2023, application-defined or object-defined error. On my charts I use the JWalk Chart Tools add in to apply labels that are linked to the cells that contain the labels. I should probably have included it in the original article. Exit Sub cancelled Do you manually edit them at all, or do you use one of the built-in options (i.e., show values)? Instead I have to switch tabs to Series and find the data ranges there, for each individual Series. I am getting the error code 445: Object does not support this action. David Rootes issue with Excel 2010 charts being destroyed after only 3 were updated with the series change. Are you trying to concatenate the series formula, like this: =SERIES(Sheet1!$B$1,(Sheet1!$A$2:$A$35,Sheet1!$C$2:$C$35),(Sheet1!$B$2:$B$35,Sheet1!$D$2:$D$35),1). The Plot Order can only be a whole number between 1 and the number of series in the chart. Dont worry about it, I think I just dont have enough VB knowledge to properly troubleshoot. This wroked fantastically saved hours of my life! Debug.Print xlExcelLink & i & : & aLinks(i) Windows macOS Edit or rearrange a series Right-click your chart, and then choose Select Data. The Y Values can be a literal array of numeric values enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). Jon, It is this action of breaking links that causes the problem. Works great for data series in charts any way to extend the idea to error bar series and embedded text in charts that is linked to the worksheet as well? I feel that Excel 2010s charting functions are worse off than in 2003 version. Therefore I renamed all my defined names simply by putting an A in front of every name. Just to say thank you copying a sheet with 15 complex charts. If you tried it and are having issues, you may have to be more clever about how you are entering your Change From and Change To text. You should avoid the urge to put multiple lines onto the same line of code separated with colons. If in fact your Name is doing something that I dont understand from my simple recreation, could you instead put the calculations into a worksheet range nearby, and use that range in the chart instead of the Name? Essentially the workaround involves checking for a Name in the X values argument of the formula, temporarily removing the argument altogether from the new series formula, then reassigning the Name using the Series.XValues property. oldPath = D:\wojtek\docs or D:\wojtek\ =5+2*3. Replace the cell reference with a static name of your choice. As there is such a sheet created in my copy file via Step 2, my data is reconnected to the copy sheet rather than the source sheet. It either appears to have completed successfully but when I check the series text it has not been replaced, or I get the error message Unknown error. Anyway, thanks for your response. I just cant seem to get it to work, no such change occurs. Cool piece of VBA. You hero! This tool is awesome!! Right click on the chart and choose Select Data, then select the series in the list and click the Edit button. Excel has crash again. or I dont know why; its just the way the new charting module is. You have boosted my productivity no end. the DOW Industrials from 1925 to 2008. All the series formulas are of the format =Sheet10!Test1 where Test1 is a defined name on the same sheet. Why not use copies of the charts? I have encountered problems with long formulas like this. If they are different, OldString is the old sheet name with single quotes if the sheet name is surrounded by single quotes in the series formula; NewString is the new sheet name surrounded with single quotes (if they are not needed, Excel will ignore them). This utility is a dream. I think weve just found a bug. I am amazed and you have just saved me hours of work. These new algorithms have been incorporated into the Change Series Formula function of the Peltier Tech Charts for Excel 3.0, both Standard and Advanced Editions. A little error trapping makes sure that there is an active chart and that OldString is valid. Any ideas? Change the data series in a chart - Microsoft Support You have just saved me HOURS of work. Select part or all of the formula, type in the text you want there instead, and press Enter to apply the changes (or sometimes to cause an error!). Jon, awesome, I was annoyed by this a few days ago, and lo behold. Also other function Okay, I managed to figure out what is causing the issue. In the ChangeSeriesFormulaAllCharts routine: Its possible to do charts on multiple sheets. If by figures you mean data in cells, copy the data, select the target range on the other sheet, and use paste special links. I have heard of the zoom affecting formatting, but since I rarely mess with it, I never think to check the zoom. It seems to have done just what you wanted, changing the string Sp500 to the string none within the larger formula string. Thanks. I have also tried several different search/replace strings. Anyway, I will try the same without commas but with the same path lenghts. To change an exact number, enter $3 in the From box, and it will only change 3, not 23. It wont change just part of a string inside the single quotes. Thank you very much for posting it. Set oData = oSheet.Range(A & iRow1 & : & A & iRow2) =SERIES( Total Forecast,{M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12},Original Workbook Name.xlsm!My_Forecast,1), =SERIES( Total Forecast,{M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12},New Workbook Name.xlsx!My_Forecast,1). I used the code for modifying the series for a selected chart (first code snippet). s = ChartSeries.Formula If you include in at the beginning or end of the Old Text, you must also include it in the New Text. Hey Jon, thanks for a great utility, it seemed to be JUST the thing I had been looking for. Great little utility, saved many hours for me! Results of my Immediate window are Before Else, insert one more Next. But I just tested in 2007, and it misbehaved when trying to change a subset of the charts on a sheet. Which is pointing to the mySrs.Formula line. So, Im moving selected worksheets from one workbook to a new workbook and want to change the series data workbook name for all my charts from the original (old) workbook to the new workbook. The error is within the action of breaking links of a parent worksheet and restarting the Excel instance. Tested on Excel 2013 without any modification, the macro (in configuration all graphs/all charts) is totally transparent. What are my options? I didnt include the algorithm here but its built into my Chart Utility. Hi Jon, just tried this in 2010 as I have 120 charts I need to update, It works great on an individual chart, but when I tried to update about a dozen on the active sheet, it only did the first 3 and then destroyed the rest. There needs to be a workbook that contains data and a workbook that contains the chart. Please Note: The charts are NOT Pivot Charts. Is there any way to fix this? Notify me of follow-up comments by email. I am a disabled Nam Era USMC vet who works as a volunteer at the local VA hospital. On a related note, is there any way to do something similar to linked pictures of charts on the same page as the chart (e.g. Meanwhile, on another sheet, nothing gets moved. and do this for 25 similar series equations. Ive described the problem and offered a couple of fixes in Ribbon Disappears from Excel. This is awesome. Else end with, it will crash if the series cant find any value (in my case I deleted the cells from which Values and Xvalues were taken). I have a question regarding data labels. If I do $cm$ to $ba$ it works. The first item, a button to extend the changes throughout the workbook, would be relatively straightforward. I then suppress the link between the copy graph and the source data. Additionally, we'll show you how to auto generate a series of Roman numbers and random integers - all by using a new dynamic array SEQUENCE function. Jeff Sub ChangeChartRowRange(minRow As String, maxRow As String) OldString, NewString) All in all I have 4 regions to report on. I dont know what it fixedI was just happy. Hi Ive been using the VBA Program to Modify All Charts on the Active Sheet for past 4 year I just need to change the worksheet name. How hard to include a third button Change Charts on ALL Sheets? One thing I have noticed is that the source values in the graphs includes the full path name, i.e. Both versions worked great. So have the code change the filename, range address, or range name as required. The link is of the form =Sheet1!$A$1, that is, absolute range references in a specified sheet. I suspect you are thinking of an altered version of this routine. Fantastic little program. As so, all references are transformed from [sourcefile.xlsx]NameOfTheTab!Range to NameOfTheTab!Range. (1) Data labels in Excel charts are not linked to a worksheet range in the same flexible way that the data point values are linked. Thank you so much, it worked perfectly! By contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21. dbg = False change to True only when sth is wrong, oldWarn = John, Jan Karel Pieterses free Name Manager utility (http://jkp-ads.com) can help you manage all names, even hidden ones. PS. Are the series actually deleted, or are they pointed to blank ranges? Sorry for the multiple post, but I think I finally have it figured out. My utility only works for the ranges in the Series tab of the dialog, which are reflected in the series formulas. Ive changed 2 numbers in 12 charts and no crashes. [] detailed description of a chart and its series formula is presented in The Chart Series Formula and How to Edit Series Formulas elsewhere on this site, but a brief description here is in [], Your email address will not be published. No need for the $ in the To box. If by figures you mean charts, copy each chart and paste onto the other sheet. Excel doesnt let you mix constant values and cell references within a single argument in the series formula. And it worked. MsgBox Nothing to be replaced., vbInformation, Nothing Entered Next s Press Ctrl+G to show this window. Step 2 : I then do a copy of the raw data by copying all cells in an empty sheet rather than copying the sheet. However, this complicates charting by multiplying the number of series needed. Is it possible to change all charts on all sheets (ignoring sheets with no charts)? Unfortunately it doesnt work for me in Excel 2007. I have files built from scratch in Excel 2007 with many Stacked Area charts (1 per sheet) with as many as 25 series in each chart. Ive been using it in Excel 2013 without a problem. I got it. Great utility! 1. Run time error 1004: When I use yor add in to change the Sheet1 to Sheet2, I lose the reference to the correct Book so all curvers are like [Book1.xls]Sheet2, [Book1.xls]Sheet2,.,[Book1.xls]Sheet2. Dominick I just tried this on my machine, in both Excel 2003 and Excel 2007, and it changed workbook name (in single quotes) to worksheet name in the series formula with no problem. MsgBox Update Complete., vbInformation Two Analysis :), Hi, So, despite your help I am still at square one manually re-doing labels for now. I have everything ready except an effective way to only change the row part of each seriescollection in chart. Any idea ? Type this into the Immediate Window and press Enter: How can I get these to change as well? If so, copy the whole sheet, charts and all. Thanks! It turns out that until the series formula is a manageable length (only around 200 or so characters), Excel doesnt pass the entire formula to VBA. The meat of this technique involves this very simple VBA command: Basically, Substitute works by replacing every instance of OldString in the input text string (Series.Formula) by NewString. I am hoping you can help me understand what is different between the series that work and the three that are not working. I have copied the code above into a macro, but every time I try to run it, it hangs at:-, mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, _ Commas in the sheet name, single quotes in the sheet name, etc. I have a table set up in Excel withe the formula to generate the dates e.g. mySrs.Formula = sNewFormula. The list/table will expand to include all of the data, and all formulas that refer to all rows in the list/table will update accordingly. Its mind numbing pain to hunt down each reference and change it by hand. I have Excel 2003 with SP3 installed so that should not be the cause of the problem. From a row of stock prices, lets say, in A1:Z1, I want to calculate Simple Moving Averages of different lengths. Enter the initial date in the first cell. I would like to replace the Master! id have the same question as jeff any way to extend this tool to error bar series? Love this add-in. I wish I could. Loop, newWarn = Dim ch As ChartObject For j = 0 To UBound(Y) There is no longer a problem. I have two workbooks that are indentical, except the project data is different. Ive annoted the full code to hopefully make it easier to understand and to spot and glaring errors. Run time error 1004: Debug.Print aLinks is : ; TypeName(aLinks) shN = Left(Y(0), InStr(Y(0), !)) Then maybe a US port connected to your cranium so the program can use the data youre thinking of. If the series data consists of all blanks, for example, you cant edit the formula using VBA. Does the utility work when you enter the snippets using the dialog? Found it. Its a write-only variable. Application defined or object defined error Names in workbook: Values Can I copy the formulas by changing = to ` copy and change back to = to keep the formulas? Perhaps I am just a noob but I had no idea that this happened. This includes the chart's SERIES formula. Doh! If the sheet names are in fact Sheet1 and Sheet2, then OldString is Sheet1 and NewString is Sheet2. After trying different options I found that if all the defined names started with the same letter, everything works fine. My issue is, I would like to be able have a sequence of the day numbers to track what number day the quiz is up to: See what i am refering to in the picture below. Did the workbook originate in Excel 2003 before you tried changing the series in 2007? It actually changed the sheet number in a couple of instances in the second chart on the sheet, but only on that chart, and not all the formulas were changed. Ray Crossing my fingers. I am running Excel 2007 SP1 on a Vista SP1 box. Using the second version of the program above, all we need to do is run the code. I have worksheets with 100.000+ rows and like to change the xvalues and values so the user can scroll using buttons. I dont know what causes the shut-down of Excel. Learn how your comment data is processed. If the cell contains a formula, you will see the formula in the cell and be able to edit it. Using the code in your own project: Is mySrs properly declared and assigned to a series? There are two ways to correct this: Youve charted data in rows 1 to 100 of your worksheet. Is this normal? Excel ran diagnosticsall ok now. Thanks Jon, greatly appreciated. As suggested I uninstalled the utility (unchecked the box), moved the file to a different directory, and then reinstalled it from the new directory (using Browse to locate the file). Use the chart customization buttons that appear in the top right corner of your Excel graph when you click on it. =Sheet1!ALim1 =2011!$B$8:$M$8+2010!$B$8:$M$8+2009!$B$8:$M8 Comments: 175, Filed Under: Utilities Tagged With: SERIES Formula, VBA, Excel chokes, because it cant read the series formula we want it to change, As Microsoft says: http://support.microsoft.com/default.aspx?scid=kb;en-us;213379. After the diagnostics in the excel options/ resource it wont bring up the web page after you press the continue button. So, e.g. The data label tools do something completely different. You cant use data from different sheets this way. I just tested this using this utility, freshly downloaded. Two Code to create a chart and set up data using the SERIES formula. However, if I delete all the charts from the copied sheet, then copy and paste the charts from the original sheet, the defined names are correct, but the sheet number is incorrect. Run the new code and tell me where it breaks. Could that additional diagnostics help? The add-in works great for changing the X value portion, but does not change the name or the Y value references.