Change # in page 1, see it in page 2. Change # in page 2, see it on page 1. How can I?

[YuraB]

I am building a financial model where I have all the forecast metric % at the coinciding page. For instance, at the income statement page I have the forecast metrics at the top with percent increase / decrease in sales written as a percentage. The next years sales are then derived by multiplying by that number. For example: (2011 sales) = [(previous years sales) * (1+forecast % sales growth)].

Well that worked out all well and good but now that I have so many metrics on multiple pages I would like to place all the metrics into 1 page at the front with the single summarized outputs. This is where I ran into a problem. At first, on the front page, I listed all the metrics that I have throughout the whole project, then I linked the percentage metrics back to the original page to where it belonged to (for instance: from the income statement).

What I want to do now is be able to change the number EITHER at the front page where all the metrics are listed, or on the original page (income statement). I do not know how to do this. Because right now, If I change any of the % numbers at the front page, that change will not go back to the other pages. Changes only go one way, from the income statement for instance. How can I make the forecast metrics go back and forth?

[Richard Buttrey]

Hi,

I don't follow this.
Presumably the metrics on the individual sheets are a calculated results dependent on other cells - presumably on the same sheet.

You confirm that the metrics on the Summary sheet are linked to the metric on the individual sheets so how do you expect to be able to update the individual metric from the Summary sheet without changing the cells on the Individual sheet on which it is dependent? The best you could do, and this would need to be a macro, would be to overwrite the individual sheet metric with a value from the summary metrics, but in doing so you would lose the formula on the individual sheet.

Please clarify.

Neither is it clear to me why you want to do this anyway.

[YuraB]

You understood it right.
I am looking for a way that two cells are somehow locked with the same values. The individual sheets would change to the input of the summary sheet, and summary sheet would change to the input of the individual sheets. That is what I am looking for.

[Jamesr571]

YuraB,

you can do this by using Macros;

The Below Code will Call The Macro I named Sheet_1 if the contents of C3 on Sheet 1change

Please Login or Register  to view this content.

The Code below will change the contents in Sheet 2 to match Sheet 1

Please Login or Register  to view this content.

then you do this in reverse order for the other Sheet (all codes are in the attached, view the coding & see Sheet 1 & Sheet 2 codes & also Module 1 & module 2)

Book1.xls

Best Regards

Jamesr571

[YuraB]

Wow, that was exactly what I was looking for!
Thank you both for your help.

Best regards,
YuraB

[YuraB]

Now that I have that figured out, what is the best way to do this for a large number of cells?

[Jamesr571]

how many cells? are we talking, are they all in the same sheet / column?