Dynamic Normalization Chart ?

I have set up a dynamic normalization chart that tracks two stock prices. You pick a time of interest and it will zero out both stock prices to that time so you can then see how the price patterns converge/diverge from each other.

The chart dynamically updates when the stock price data is included within the worksheet but becomes static when the stock data is being externally linked. Is there anyway to keep the chart dynamic when the stock data is being externally linked? More specifically in the attached spreadsheet, if ETFC and GM tabs are saved as separate spreadsheets the chart is no longer dynamic.

Does anybody have any ideas how to keep this chart dynamic when the stock data is being saved in a separate spreadsheet?

Thanks,
Impala096

It has been about a week and still no response. Safe to assume this isn’t possible then?

What do you think oldchippy?

You seem quite knowledgeable when it comes to charting tricks and tips. Basically if a formula could be entered as part of the source data i think i would be able to externally link the data while keeping it dynamic.

I just don't know where to start and i could use some help or suggestions.

Thanks,
Impala096

I saw your post, but I'm not sure if I fully understand what you are trying to do. If you were to move your GM and ETFC tab to a different worksheet, you can change your graph to pull from the new worksheet holding the data by altering the Source Location (in this case Book3)
=[Book3]ETFC!$I$123:$I$201

If this is not what your problem is, maybe you can give more detail. I think we might be able to figure something out.

Hi Denver,

Thanks so much for your reply and keeping this question alive still!

The following steps details the problem I’m having.

Step 1: Open the attached file and under the Summary tab, input time 10:00 in cell K1. You should see the chart automatically update and zero out the two stocks to this time.

Step 2: Create a new blank worksheet by clicking on File --> New… Blank worksheet.

Step 3: Go back to the DynamicNormalization Chart.xls worksheet and highlight the ETFC tab and right click --> Move or Copy. The Move or Copy dialog box should appear.

Step 4: Under Move selected sheets To book: , highlight the newly created worksheet and click OK (don’t change anything under Before sheet: ). The ETFC tab is now on its own separate worksheet and the chart’s source data has been automatically updated.

Step 5: Save the spreadsheet that has the ETFC data and close it.

Step 6: Return to the DynamicNormilization Chart.xls worksheet and under the Summary tab, input time 12:00 in cell K1. Now, the chart isn’t updating automatically as it did in Step 1 (only GM data is automatically updating but the externally linked ETFC data remains static).


This is the problem I’m facing. Note that if you were to open up the ETFC data again, the chart will again automatically update and work fine. Obviously though I don’t want to have all my separate stock data spreadsheets open at once and want the chart to remain dynamic while they are closed.

If anyone has any suggestions on how to create a dynamic chart like this one when the data is being externally linked I would be forever grateful!

Thanks,
Impala096