Problem: Auto-updating charts when new data/cells added

[AndrewS]

Hi All,

Greetings from Canada!

CONTEXT:
I've spent the last 4 months building phase 1 of 3 of an enormous, highly sophisticated tool for my new long term employer and hopefully future business partner, an investment advisor and financial planner. The program is designed to take all of someone's financial info and output all kinds of customizable retirement plans, investment strategies, portfolio construction guidelines, and mainly their hypothetical portfolio compared against the last hundred years of market data represented by scenarios showing how the plan would have worked had it started in every year during the last 100 (to help give some probability of success).

PROBLEM:
Every quarter the vast sets of fund and market data present in the form used to generate these scenarios requires updating, however once manually input, the charts don't include it in their projections.

I need the graphs to automatically recognize the addition of new cells (under the old) and adjust the data range lookup to include them.

WHAT I'VE TREID:
I've found a couple savvy ways to look up the last cell in a range and tried embedding that in the graph lookup section, however that section doesn't seem designed for embedded code. At least not the way I tried it.

I've also tried expanding the graph's lookup area to include a bunch of empty cells under the existing data but that just leaves giant empty space on the graphs, and not a solution.

HOW TO TEST:
The easiest way is to make a simple chart populated by two columns of data (say dates and values), and then make it so when you add new cells undernearth, the chart would pick them up automatically.

Really looking forward to some brainy help!

 

[Somendra Misra]

Hi Andrew,

Welcome to the forum.

See if this link helps you.

http://chandoo.org/forum/threads/updating-data-range-and-chart-automatically.20964/

Or else upload your sample file.

Regards,

 

[AndrewS]

Sample file uploaded.

I wasn't able to find what I needed in that thread, though the first link did include some useable pieces. I basically need it to do the same thing but not to remove entries as it adds new ones.

 

[bobhc]

Good day and welcome, is the upload as you require?

 

[AndrewS]

This is possibly the most painfully ugly chart I've ever seen but it works!

The only problem is I don't understand how it works so I can't replicate it..how is the chart automatically picking up the new data? The code looks the same as the default array range code, but when more data is entered into the column the array somehow adjusts itself..

 

[bobhc]

I did the chart to grab the attention....seemed to work....

Just convert the raw data to a table when you reach the last right bottom .input cell hit the tab key and it will insert a new row into the table and any new data will be updated to the chart.

Have ago enter cursor in bottom empty left cell, input data into the two cells and hit tab key.



POST MOVED TO DATA VISUALIZATION.

 

[AndrewS]

Beautiful! Took me a minute to follow your outline but I got it working, thank you for explaining.

Are you ready for my ensuing problem?

As I enter data over time the hypothetical portfolio size will grow, and it's growth is being averaged over time via the RRI function to output the average rate of return over time. Once I've entered new data into the fields and the tables pick it up, the new RRI variable will need to be automatically picked up: it must be able to count the amount of cells in the table array and use that number of counted cells as the RRI variable to form a proper average.

Let's see if you can crack this one

 

[bobhc]

Many members have specific areas of interest in the forums and as such may not visit all areas that is why it is best to post in the area most relevant to the subject you are hoping for help on.
In the case of your last question it would be better in the "ask an excel question" forum, even though it may be to do with the same workbook the rule that works is....new question new post.



(put the table name (you did name the table) in to the formula)

 

[Somendra Misra]

@AndrewS

I think Bob had helped you out with your charting problem (Thanks Bob) , as per your new problem, I don't have Excel 2013 to exactly try out below suggestion, but try this.

Try using below formula:

=_xlfn.RRI(COUNT(Table1[Returns]),D2,LOOKUP(99^99,$D$2:$D$100))

Regards,