Intense Chart Plot

[Chiccada]

Hello all,

I have what I percieve to be a bit of a task infront of me. Firstly, I shall explain where and how the data is stored and then what I need to do with said data.

I have some figures that are held in pivot tables in seperate workbooks all held in the same file on the same sever.

I need to plot a line graph which shows each total of each client for each week for the figures heald in each workbook. There are 12 workbooks in all and one will be added weekly.

The X axis would show the week and the Y axis would show the amount. For example, I have a client called excelmart. I would need to show in a line chart for the total amount of outstanding monies owed per week. The data for this being drawn from each of the 12 workbooks already compiled previously.


Is there a way of doing this with out me needing to learn visual basic or is there an alternative way to do it and get the same results?

Many thanks in advance for your time.

Rikki

[Bryan Hessey]

Hello all,

I have what I percieve to be a bit of a task infront of me. Firstly, I shall explain where and how the data is stored and then what I need to do with said data.

I have some figures that are held in pivot tables in seperate workbooks all held in the same file on the same sever.

I need to plot a line graph which shows each total of each client for each week for the figures heald in each workbook. There are 12 workbooks in all and one will be added weekly.

The X axis would show the week and the Y axis would show the amount. For example, I have a client called excelmart. I would need to show in a line chart for the total amount of outstanding monies owed per week. The data for this being drawn from each of the 12 workbooks already compiled previously.


Is there a way of doing this with out me needing to learn visual basic or is there an alternative way to do it and get the same results?

Many thanks in advance for your time.

Rikki

sounds reasonably ugly and vague.

In the worksheet that is to hold the chart, a list of Workbooks in column A, if you know the full list you can insert the full list and Hide any for-the-future rows.

for each figure in each row use something like

=Indirect("["&A1&&"]Sheet1!A1")

to gather data from an open book, or see the MoreFunc addin to gather data from a book that is not open.

another possibility is to use a Lookup, something like

=VLookup(A1,Indirect("["&A1&&"]Sheet1!A:B"),2,False)

but you would, of course, need some related item for which to search.
The VLookup operates on closed or open books.

The Chart could then be set to Plot Visible Only, to not use Future rows, and would require minimal maintenance.

Does this help?
---