Adding to table rows whilst keeping my chart dynamic.

naquinn

Board Regular
Joined
Mar 5, 2009
Messages
55
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.


chart.PNG



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.



Table.PNG



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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,845
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 5, 2009
Messages
55
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.....!
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,845
Office Version
  1. 365
Platform
  1. Windows
Thanks for the additional information. (y)
I've now marked your last post as the solution. :)
 

Forum statistics

Threads
1,148,241
Messages
5,745,583
Members
423,963
Latest member
lwilson3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top