If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple Charts from single instance of data
billdierker wrote:
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 |
Thread Tools | |
Display Modes | |
|
|