Charting information from nultiple work books

[Pam]

I have 4 different workbooks of information, with 12 (1 per month) tabs in
each workbook. On each tab I have about 40 lines of information that need to
be graphed. I am having great difficulty doing this. I am relatively new to
excel, so it's a little over whelming. Each tab has 11 expenses, 7 income
revenues for the current year, the previous year, YTD current year, YTD last
year, Variance and YTD variance. There a lot of informatin. i'm trying to
graph the information on a pie chart, trailing for 12 months. I need help
with how to display the right information in the best way possible. Anyone
have any ideas??

 

[Jon Peltier]

how to display the right information in the best way possible.

Well, I don't know what the right information is, but a pie chart can't
possibly be the best way if you have more than about five pieces of data.

Are you trying to display as a function of date? If so, you will have to get
all the data onto one worksheet. An easy way to do this that is pretty
reliable and accommodates changing information, is to copy each range of
data from the individual sheets, then select a cell in a new summary
worksheet, and use Paste Special to paste links.

Time series data (data that is tracked over time) is usually best done with
a line chart, though sometimes a column chart is okay.

- Jon

 

[Pam]

Well I am going to put the useful information into one master sheet. I think
that will work the best. Had I known that it would have made less work, oh
well. I also want to do a trailing 12 line graph, to calculate net
income/loss over a 12 months span. I don't want to have to change my source
data every month by going in and chaning it ymself. Is there a way that this
can be done where it will automatically change when I had the new numbers
each month??

 

[Jon Peltier]

You might be able to set up INDIRECT links, such as I describe in this
tutorial:

http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html

- Jon