Charts - From Consolidated to single unit

[Metallo]

Hi,

In my Consolidated WB I have 7 Charts and 1 database I have created as a
data source. In fact, in the DB, there are the formulas which get the data
from ten different WB.

Eample:

Consolidated WB picks up the data from WB1,2,3,4,5,6,7,8,9,10

Result = 7 Charts that represent the consolidation of the 10 WB.

Now, what I want to do is to copy the 7 Charts to every WB (1 to 10)
including the DB and update the data so that I can see how every company is
performing..

Easy to say, but difficult to do, I tried different ways but it looks like I
have to do a lot of manual work and I don't believe it

The Consolidate DB is structured more or less this way:

Column
A,B,C,D,E,F
Company
1
2
3
4
5
6
7
8
9
10

Can anybody help me in finding a quick and easy solution to this?

Thanks
Alex

 

[Metallo]

Hi,

Any idea?

Thanks
Alex

 

[Jon Peltier]

Alex -

Do you want to show the consolidated chart in each workbook? Is the
chart on its own chart sheet? If so, this works nicely.

1. Right click on the sheet tab, select Move or Copy.
2. Select the target workbook (it must already be open), check the
Create a Copy checkbox, and press OK.

A copy of the chart sheet appears in the target workbook.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jon Peltier]

Hey Alex -

It's Monday morning, eh?

- Jon

It's many hours since I posted my question and I'm usually spoilt by your quick replies, is that so difficult to solve or you need more info?

Thanks again
Alex

:

 

[Metallo]

Jon,

No, it's much more that this.

Let's try with an example.

The consolidated DB into the WB"Cons" has got 10 Companies into it, but to
simplify I'll put 5:

A B C D E
Company 1 3 1 2 0 0
Company 2 5 1 2 10 13
Company 3 4 2 3 5 6
Company 4 2 1 4 2 5
Company 5 1 1 1 3 6
--------------------------------------
Results 15 6 12 20 30
=========================

The above mentioned numbers are the sum or the comparison of the data of
the companies and from them I have created 7 different consolidated Charts.

Now, what I want to do is to go back to each WB of the single company, so,
Company 1, Company 2 etc... and copy the same charts and DB to get the same
type of chart but obviously with the numbers of the single company.
In the above mentione example:

WB Company 1 - New modified DB

A B C D E
Company 1 3 1 2 0 0
--------------------------------------
Results 3 1 2 0 0
=========================

WB Company 2 - New modified DB

Company 2 5 1 2 10 13
--------------------------------------
Results 5 1 2 10 13
=========================

WB Company 3 - New modified DB
Company 3 etc.
--------------------------------------
Results etc.
=========================

When I copy the Consolidated charts and DB to Company 1 (or any of the
others) the first thing I need to do is:

1) Take out the data of Company 2,3,4,5 from the DB
2) At this point, the Charts mess up because if the data of the consolidated
chart where coming from a ratio between, say, cell A1(Company1) and A6
(Results), cutting off the other companies A1 remains there but what was
before A6 goes up to A2 and therefore the chart does not work anymore.
Even worst when I do the same process for Company 2, in fact, A2 goes up to
A1 and A6 to A2, therefore, all is messed up again.

This is a very simple example, but in the reality the numbers are much more
complex and the sheets involved are more than 100!

Basically, I want to get the same charts for the single companies with their
individual numbers, I cannot think that I have to select again the source
data to the new positions of the DB, I think instead I should get to a
situation in which I just need to "update" the link.

To do this manually it would involve days and days of hard work, while I'm
sure you can help to simply to a few hours.

Hope this is clearer and gives you the chance to provide me with a solution.

Thank you!
Alex

 

[Metallo]

Is there anybody able to help??

Alex

Jon,

No, it's much more that this.

Let's try with an example.

The consolidated DB into the WB"Cons" has got 10 Companies into it, but to
simplify I'll put 5:

A B C D E
Company 1 3 1 2 0 0
Company 2 5 1 2 10 13
Company 3 4 2 3 5 6
Company 4 2 1 4 2 5
Company 5 1 1 1 3 6
--------------------------------------
Results 15 6 12 20 30
=========================

The above mentioned numbers are the sum or the comparison of the data of
the companies and from them I have created 7 different consolidated Charts.

Now, what I want to do is to go back to each WB of the single company, so,
Company 1, Company 2 etc... and copy the same charts and DB to get the same
type of chart but obviously with the numbers of the single company.
In the above mentione example:

WB Company 1 - New modified DB

A B C D E
Company 1 3 1 2 0 0
--------------------------------------
Results 3 1 2 0 0
=========================

WB Company 2 - New modified DB

Company 2 5 1 2 10 13
--------------------------------------
Results 5 1 2 10 13
=========================

WB Company 3 - New modified DB
Company 3 etc.
--------------------------------------
Results etc.
=========================

When I copy the Consolidated charts and DB to Company 1 (or any of the
others) the first thing I need to do is:

1) Take out the data of Company 2,3,4,5 from the DB
2) At this point, the Charts mess up because if the data of the consolidated
chart where coming from a ratio between, say, cell A1(Company1) and A6
(Results), cutting off the other companies A1 remains there but what was
before A6 goes up to A2 and therefore the chart does not work anymore.
Even worst when I do the same process for Company 2, in fact, A2 goes up to
A1 and A6 to A2, therefore, all is messed up again.

This is a very simple example, but in the reality the numbers are much more
complex and the sheets involved are more than 100!

Basically, I want to get the same charts for the single companies with their
individual numbers, I cannot think that I have to select again the source
data to the new positions of the DB, I think instead I should get to a
situation in which I just need to "update" the link.

To do this manually it would involve days and days of hard work, while I'm
sure you can help to simply to a few hours.

Hope this is clearer and gives you the chance to provide me with a solution.

Thank you!
Alex

company

 

[Jon Peltier]

Hi Alex -

Rather than copying the charts, pasting them, and readjusting the source
data range, you could just make new charts using some custom chart types
you create yourself. For example, take one of your charts from the
consolidated workbook. Right click on it, choose chart type from the pop
up menu, click on the Custom tab, click user defined, then Add. Type a
name and description, and press okay. Next time you run the chart
wizard, you can select this chart type.

Alternatively, you could write a macro that makes a chart or series of
charts from the active sheet. However many companies are listed in the
sheet, that's how many can be plotted.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______