How can I make multiple charts of the same information but different lines of business

[beagle50]

Hi All,

I am an analyst at a large company with ~18 lines of business. When I create reports I often need to make a bar graph for each line of business. It is very time consuming to switch the data source and recreate the graph 18 times (I often do quarterly and monthly reports).

Any ideas on how to make this process more automated?

Any help is much appreciated!!

-Mr. T

[FDibbins]

You could create a table that pulls out the business info, based on you selecting a specific business? (If you set this up properly, and you want to see multiple charts, you could probably copy/paste the table and chart, then just changethe table on each copy?)

[TMS]

I would suggest that a typical way is to have a drop down box that lists the lines of business. Each line of business would have its own raw data sheet and data/totals to be graphed. You would then have a "generic" dataset that refers to the business line data using INDIRECT functions to link to one of the lines of business worksheets.

Then you create your graphs from the generic dataset.


Regards, TMS

[beagle50]

Thank you! This solution seems to work pretty well. The only issue is the title doesn't seem to update and when there is a blank it still creates a label. I tried to upload a screenshot. Any ideas?
Graph .JPG

[TMS]

A screenshot isn't very useful in helping to work up a solution.

Please provide a sample workbook with some typical data.

Regards, TMS

[FDibbins]

Not sure which appoach you took - TMS's is the same in principal but different in execution. Creating a table based on a drop-down, you could set it up to not have blank rows (using index/small/if array)

[beagle50]

I used your approach FDibbins and it seems to be working well for the most part. Thank you for your help!