Jan 19 2022 08:43 AM
I need to use an offset formula in the graph so when data is entered for Jan 22 through July 22 it will automatically update the graph. Any formula ideas?
Series o
Jan 19 2022 08:48 AM
Please check Two top easiest ways to create a dynamic range in Excel chart (extendoffice.com) or google "excel dynamic charts" for other samples
Jan 19 2022 08:49 AM
SolutionSee How to Create a Dynamic Chart Range in Excel in particular the section "Using Excel formulas".
Jan 19 2022 09:32 AM
Jan 19 2022 11:13 AM
Column C will always have all values, so you should use the number of non-blank values in column F in the definition of the named ranges for both the x-values and the y-values.
Jan 20 2022 07:45 AM
Jan 20 2022 11:25 AM
Do you see an area with "Drag and drop here or browse files to attach" below the box where you compose a reply? If so, add the workbook there.
Otherwise: could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then make the copy available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Post a link to the uploaded and shared file in a reply here.
Jan 20 2022 12:45 PM
Jan 20 2022 12:47 PM
I fear that you haven't shared the file - I get a message that the item doesn't exist or isn't available anymore.
Jan 20 2022 12:52 PM
Sorry, same error. If you wish, you can send the file to hans dot vogelaar at gmail dot com
Jan 20 2022 12:55 PM
Jan 20 2022 01:27 PM
Thanks - I received it in good order. I have created dynamic ranges for the charts on the Secondary Measures sheet. You can view their definitions in Formulas > Name Manager, and see how they are used by selecting a chart, then clicking Chart Design > Select Data. Click on Edit for any of the series or the horizontal axis labels in the dialog.
Jan 21 2022 06:35 AM
Jan 21 2022 06:51 AM
You need to create three dynamic named ranges for each of the charts:
- One for the x-values
- One for the YTD% series
- One for the Goal series.
You can see how they are used when you inspect the Select Data dialog for one of the two charts that I edited.
Jan 21 2022 09:07 AM
Jan 21 2022 12:04 PM
You have to define the named ranges first:
You must then edit the x-values and y-values of the charts to use these names.
This is for the top chart on the Secondary Measures sheet:
For example, for the x-values:
And the y-values of the YTD% series:
Jan 19 2022 08:49 AM
SolutionSee How to Create a Dynamic Chart Range in Excel in particular the section "Using Excel formulas".