Axis Labels Range

[terrytibbs]

Hi

I have created a dynamic chart that updates as I select the date range from a drop down menu.

Each data series in my chart refers to a named range in my worksheet - the named range is a formula that updates the data range as the drop down is changed

It works perfectly well - except when I close the worksheet and then re-open it the named range referred to in the axis label range has changed slightly so that it no longer works.

For example - what it should look like when it works:

Please Login or Register  to view this content.

The workbook is called: QuoteCalcs.xlms
The sheet within the workbook: Parameters

When I close the worksheet and then reopen it the code changes to the below.

Please Login or Register  to view this content.

I have to then manually change [0] to Parameters to get them all to work which defeats the whole purpose

I am not sure if I need to fix the Parameters sheet within the code - not sure how to do this??

Any thoughts/comments much appreciated?

I have also posted this on mrexcel but the suggestion I received didnt work

[TMS]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[terrytibbs]

Thanks

I enclose the spreadsheet.

On the parameters tab is the graph.

Each data series on the graph refers to a named range. It is this reference to the named range that I want to fix. It seems to change slightly every time I close the spreadsheet which means I need to manually update each named range for each data series in the graph each time I open it.

Any suggestions very much appreciated as this is really bugging me!

[TMS]

After looking at the sample file, my first guess is that the issue is that the Data tab doesn't have all the dates into the future. Hence, when you open the workbook up on a new day, =TODAY()-2 might not exist, and so it doesn't exist in the InterimData tab which is being charted.

At least that was the issue that I had when I opened your workbook. Excel complained about formula issues on the Parameter sheet and that turned out to be the fact that the dates stopped at the 18 January 2019. Once I realised that and fixed it, the graph looks OK. The Named Range mydatesmy, ='YahooStockQuotes8.xlsm'!mydatesmj, comes out as =INDEX(interimdata!$A:$A,MATCH(Parameters!$E$15,interimdata!$A:$A,0)):INDEX(interimdata!$A:$A,MATCH(Parameters!$E$16,interimdata!$A:$A,0))

[terrytibbs]

Thanks TMS

Really appreciate your help.

I make the changes that you suggest but when I close the spreadsheet and reopen it the named range mydatesmj goes from: = 'YahooStockQuotes8.xlsm'!mydatesmj
To =[0]!mydatesmj.

Which means it needs to be manually changed to work again - is there anyway to fix YahooStockQuotes8.xlsm'!mydatesmj

I guess it's because when I close YahooStockQuotes8.xlsm the named range is referring to a closed sheet?

Thanks