Multiple Charts from single instance of data

[billdierker]

I am trying to produce multiple charts from one instance of data. The
data is emailed to me in "raw form" - that is, imbedded as message text
in the body of the message. The data is rather simple; consists of 3
fields per record as follows:

Account# Actual Plan
1 75 100
2 100 100
3 1250 1000
etc.

This data is sent to me once every month. Pretty simple in terms of
data... Each account number has an actual (spend) and a planned
(spend). In the example above, account 1 is "under plan", account 2 is
"at plan", and account 3 is "over plan". As stated, the data is emailed
to me in the format/layout above and is a requirement (in other words,
I cannot change how I acquire the data). In short, each month I am
transferring the raw data from email into an Excel spreadsheet for
purposes of accumulating and charting over time. One requirement I have
is to transfer the data from email to Excel without re-typing. Another
requirement is that data in the workbook must be single instance - that
is, no duplication between sheets, etc. So, month after month, I
accumulate incoming data into Excel and it looks like this:

January February March
etc.
Account # Actual Plan Actual Plan Actual Plan etc.
1 x y x y x
y etc.
2 x y x y x
y
3 x y x y x
y
etc.
etc.

It's rather straightfoward to produce the first graph - a view of
actual vs. plan for all accounts for 1 month. For example, all accounts
on a single chart for January. I get tripped up when trying to produce
the "second" chart - a per chart account of the YTD performance of
actual vs. plan. This starts out easily enough by selecting (CTRL+cell,
etc.) of each "Actual" for account 1. With 12 non-contiguous cells
selected, I am able to use Chart Wizard and construct a basic chart of
the actual values, by month, for account 1. This approach is where
trouble starts - selecting 12 non-contiguous cells results in a SERIES
function consisting of ~458 characters and, although is acceptable in
Chart Wizard, the size of the SERIES function gets truncated by the
chart tools for ongoing chart management. For example, when I select
the chart and right-click to "Source Data", the 458-ish characters in
the SERIES gets truncated and rendered invalid - I am unable to
successfully exit the chart maintenance tools with any changes due to
this induced error. Now, I could be completely misinterpreting what's
actually happening - I'm not an Excel guru. In any event, my conclusion
at this point is my approach needs to change - that is, I cannot
utilize CTRL+cell to select 12 non-contiguous cells and I need a
different approach. This is where I'm stuck. Help!

I'm willing to re-think/ scrap anything I've done up to this point from
a prototype perspective. I have 2 thoughts on this; first, I'm sure
Excel can accommodate what I'm trying to accommodate, and second, I
believe I do not know enough about Excel (internals) to make a wise
choice on how to proceed. I'm looking for advice/ direction.

Thanks!
Bill Dierker

 

[dvt]

I accumulate incoming data into Excel and it looks like
this:

January February March etc.
Account # Act. Plan Act. Plan Act. Plan etc.
1 x y x y x y etc.
2 x y x y x y etc.
3 x y x y x y etc.
etc.

I get tripped up when trying
to produce the "second" chart - a per chart account of the YTD
performance of actual vs. plan.

I can think of two possibilites.

First, how about two helper sheets? Your previously described sheet would
be Sheet1. The second sheet, containing only actual data, might look like
this:

Jan Feb Mar etc.
Account # Act. Act. Act. etc.
1 x x x etc.
2 x x x etc.
3 x x x etc.
etc.

The third sheet would be analogous but contain only Planned data. The data
would be linked from the first sheet. So for example, Sheet2 cell A3 would
contain the formula =Sheet1!A3. You would need to enter the data (as you
currently do) on Sheet1, while the other sheets would be automagically
populated. Now the charting is pretty simple, but the worksheet may be
large depending on the number of accounts you are tracking.

The second possibility is to use a named range to select your alternate-cell
series. Insert | Name | Define, give your range a name (i.e. Account1),
then select your 12 cells. Now plot the data using the named range in the
y-axis values. To use a named range in a chart, enter something like this:

='FileName'!RangeName

Put that formula in the Source Data | Series tab | Values box of a line
chart.

Dave

 

[billdierker]

Hello Dave!

Well, your second suggestion seems to have done the trick! I must admit
- I did not know about the 'Filename'! portion of the syntax which
seems to have made all the difference... I had tried JUST the named
range with an equal-sign (such as, =MyRange) and it was evaluated and
converted to the actual range - which of course suffered the same
truncation effect. The filename prefix seems to [somehow] overcome the
limitation and the name remains as the name itself and does not get
evaluated/ truncated.

One caveat I can add; I must define the named range by selecting the 12
non-contiguous cells and typing the name into the Name Box (to the left
of the Formula Bar). Even the Insert | Name | Define approach cannot
overcome the truncation!

So, in summary, if I define the named ranges by selecting the target
cells and entering the name into the Name Box AND utilize the
'Filename'! syntax for connecting to the charts, it looks good at this
point!

Thanks so much for your help!
Bill

 

[Tushar Mehta]

Reorganize your data into a single set such as:

Month Acct Actual Plan

Just add each months data at the bottom of the existing data (use Copy
paste from the email message).

Once the dater are organized in a relational database format, the
reports you want are trivial. Among other options check out PivotTables
(and Pivot Charts).

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office