Adding to table rows whilst keeping my chart dynamic.

[naquinn]

Hello, I'm stuck trying to fix a problem connecting two tables to a chart.

I have a simple chart with two series of 'options for trading'. Calls and Puts.

Each series is feed from its' own table. A table for Calls and a separate table for Puts.

Both table look like this. They are formatted into tables.

I then have a start and end date range selection box where I can use data validation to create a couple of dropdown lists to dynamically change the date range of the chart.

the start date and end date boxes have the same data validation formula. Cells B30 and C30

=Ag_Calls$M$2#

That's the reference to my data prep for the chart which is pulling out the correct columns.

I can connect the chart dynamically using these named ranges.

AG_CALLS_DATE
=Ag_Calls!$M$2#


AG_CALLS_DATE_CHART
=INDEX(AG_CALLS_DATE,MATCH(Dashboard!$B$30,AG_CALLS_DATE,0)):INDEX(AG_CALLS_DATE,MATCH(Dashboard!$C$30,AG_CALLS_DATE,0))

AG_CALLS_OPEN_INTEREST
=Ag_Calls!$O$2#

AG_CALLS_OPEN_INTEREST_CHART
=INDEX(AG_CALLS_OPEN_INTEREST,MATCH(Dashboard!$B$30,AG_OPTS_DATE,0)):INDEX(AG_CALLS_OPEN_INTEREST,MATCH(Dashboard!$C$30,AG_OPTS_DATE,0))

The exact same is replicated for the 'Puts' series.

This all works well. I can change my dates for start and end and the chart y axis date range and both series change ok.

My problem occurs when I start to add a new line of data to my tables.

If I want to add a new days data to the one of the tables I can. However, because the second table doesn't have that new line added in to keep the row count the same between both tables I get the standard cell reference error saying there is something wrong with the links.

My guess is that the way I have it set up looping through named ranges doesn't account for the fact that both tables my not contain the exact same number of rows. I'm struggling to understand how to do about fixing this problem. I tried to have one series on one axis and the other on a second for both x and y axis, but it just doesn't fix it. Its like excel needs the exact number of rows in each table to condense it down in one chart.

Please could anyone point me in the right direction? I'm wondering if i have to start researching power queries and linking tables together in a pivot table?

Many thanks

Nick

 

[naquinn]

I figured it out. please ask if you'd like to know how I fixed it

 

[Peter_SSs]

Glad to hear you got the solution.

Do you mind posting about your solution? Then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[naquinn]

Apologies, no probs. What I hadn't fully understood was that the y-axis (the date) had to come from the same table as the series. In my named ranges I'd used a different tables' date as a kind of 'master' date. The charts don't like this.

Here is what my index and match functions looked like

=INDEX(AG_CALLS_OPEN_INTEREST,MATCH(Dashboard!$B$31,AG_OPTS_DATE,0)):INDEX(AG_CALLS_OPEN_INTEREST,MATCH(Dashboard!$C$31,AG_OPTS_DATE,0))
=INDEX(AG_PUTS_OPEN_INTEREST,MATCH(Dashboard!$B$31,AG_OPTS_DATE,0)):INDEX(AG_PUTS_OPEN_INTEREST,MATCH(Dashboard!$C$31,AG_OPTS_DATE,0))


Here is the fix:
=INDEX(AG_CALLS_OPEN_INTEREST,MATCH(Dashboard!$B$31,AG_CALLS_DATE,0)):INDEX(AG_CALLS_OPEN_INTEREST,MATCH(Dashboard!$C$31,AG_CALLS_DATE,0))
=INDEX(AG_PUTS_OPEN_INTEREST,MATCH(Dashboard!$B$31,AG_PUTS_DATE,0)):INDEX(AG_PUTS_OPEN_INTEREST,MATCH(Dashboard!$C$31,AG_PUTS_DATE,0))

The second bit of the match functions now refers to the calls and puts tables that have their own date columns. I guess it's more self contained.

Anyway, this allows me to add more data to each table without getting the referencing problem.

It's a learning curve.....!

 

[Peter_SSs]

Thanks for the additional information.
I've now marked your last post as the solution.