SOLVED

Offset Formulas

Occasional Contributor

Vicky405_0-1642610368219.png

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

16 Replies

@Vicky405 

Please check Two top easiest ways to create a dynamic range in Excel chart (extendoffice.com) or google "excel dynamic charts" for other samples

best response confirmed by Vicky405 (Occasional Contributor)
Solution

@Vicky405 

See How to Create a Dynamic Chart Range in Excel in particular the section "Using Excel formulas".

So Series 1 is column c and f, how does an offset formula work with 2 different ranges/

@Vicky405 

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.

I am still having issues. Can I send you the sheet and see if you can figure out a good formula? I have many other graphs I need to apply this formula to. d

@Vicky405 

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.

@Vicky405 

I fear that you haven't shared the file - I get a message that the item doesn't exist or isn't available anymore.

@Vicky405 

Sorry, same error. If you wish, you can send the file to hans dot vogelaar at gmail dot com

Just Sent to email. Let me know if you do not get it. Thanks again

@Vicky405 

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.

Am I replacing the current YTD and Goal formula with the formulas you created? Or am I just adding to the Series?

@Vicky405 

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.

Ok, so I have never used Name Manager before, once the named ranges are listed in the Name manager it will impact the charts below correct?

@Vicky405 

You have to define the named ranges first:

S1083.png

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:

S1084.png

For example, for the x-values:

S1085.png

And the y-values of the YTD% series:

S1086.png