Dynamic range to update chart, multiple series

[staples]

Hi there

I am creating a line chart with 2 series from seperate sheets. Each chart is populated from a single row on each sheet. Each sheet has the same account numbers running down column A. The date runs across row 1 in each sheet. One sheet shows, lets say, cash and the other cheque.

A chart needs to be created for many of these accounts so I am therefore, trying to automate this process a little. I have found a way to do this using dynamic ranges and a macro, however this only works for single series charts.

The only way I can get this to work for charts with two series is if the account numbers are in the exact same order in both sheets.

I have attached the file, any help would be much apprieciated
Thanks

[Andy Pope]

You need to alter the named ranges slightly so the Row offset value is also dynamic based on the result of a MATCH formula.

I have add a data validation cell to the cash sheet that allows you to pick the account to report. The same cheque report is done on the other sheet.

[staples]

Hi Andy

Thanks for the reply, that is working great
Could I just ask, would there a be way to automatically update the chart when I added a new days worth of data in the end column?

Thanks again
Rich

[staples]

Hi again,

Sorry to keep pounding you with questions!

I was wondering would this method still work for thousands of rows of data? On the example I only listed a basic 8 account however I will often have thousands of rows to work through.

Would it still be achvieable through this method or do you perhaps know of any
other way?

The method I found was using a macro which, when you double clicked an account in the Cash Data sheet, it would update the chart, however I couldn't figure out how to link to correctly to the cheque sheet like you managed to do.

Thanks
Rich

[Andy Pope]

The named range doesn't require the +1 as the Match formula does not include A1 in the source range.

=OFFSET(CASHDATA!$A$1,CASHDATA!$D$21,1,1,10)

You can add extra days by using a formula to count the number of days in rows.

=OFFSET(CASHDATA!$A$1,CASHDATA!$D$21,1,1,Counta(1:1)-1)

And you can adjust the match forumla to include a larger range.

[staples]

I have been having a play around and still having a few problems if anyone could please help me out.

The problem I have is that because I am wanting to use two series in my chart, when I use the index formula the series aren't always linking to the same account. As the index is based on a row number, if one table has more or less accounts than the other, then the index formula selects the wrong account and therefore displays the wrong data on the chart.

The chart does show 2 series however, one line if for a particular account and the other line is for differnt account.

I am unsure how to grab the correct data for each series. I know vlookups are useful for this, however I do not know if it can be achieved in that manner.

It's a little tricky to explain in words I'm affriad so have attached my most recent worksheet.

Thanks again
Rich

[Andy Pope]

The index value in B1 is the index for the data on sheet Account01.
You need to create an index value for the data on sheet Account02.

In ChartData!G1 use this formula

=MATCH(INDEX(Account01!A2:A44,B1,1),Account02!A2:A41,0)

And change the references to $B$1 in row 4 to $G$1

note you will get a few #N/A records as there is not a 100% match between the 2 lists.

[staples]

Working perfect!

Thanks
Rich