B
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
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