cfogelberg
New Member
- Joined
- Mar 23, 2012
- Messages
- 1
Hello,
This is an Excel 2007 and Excel 2010 question (I use both, and guess the answer will be basically the same for both): In the same way I can paste a value into formulae with "paste value", is there any way to paste a cell reference?
For example, I've attached a simple example below which shows the same setup three times.
Excel 2007
In the first example, we have the pre-existing sales year on year for 4 years.
However, we realise we need to adjust the sales year on year by some growth rate. The second example is a first attempt which copies and special pastes by value the multiplier 110% into each of the future years. This changes all of their formula to be multiplied by 1.1.
But actually, what I want is to be able to paste a reference into the formula of each of the cells in row 9 (e.g. "*D9") so that I can change the growth rate for all of them easily by changing the value in the "Growth" cell.
Doing a special paste by formula doesn't work (see the third example, where the multiplier for cells E15:G15 isn't in the D column anymore), and while I can manually edit the cell formulas when there's only 4 of them it's harder when there's 4000.
Many thanks in advance for your help,
Christo
This is an Excel 2007 and Excel 2010 question (I use both, and guess the answer will be basically the same for both): In the same way I can paste a value into formulae with "paste value", is there any way to paste a cell reference?
For example, I've attached a simple example below which shows the same setup three times.
| Excel Workbook | ||||||||
|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | |||
| 2 | Basic template | |||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | YoY value | 100 | 100 | 100 | 100 | |||
| 6 | ||||||||
| 7 | Paste special - Value and Multiple | |||||||
| 8 | Growth | 10% | ||||||
| 9 | Multiplier | 110% | ||||||
| 10 | YoY value | 100 | 110 | 121 | 133.1 | |||
| 11 | ||||||||
| 12 | Paste special - Formula and Multiple - DOESN'T WORK | |||||||
| 13 | Growth | 10% | ||||||
| 14 | Multiplier | 110% | ||||||
| 15 | YoY value | 100 | 100 | 100 | 100 | |||
Sheet1 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E5 | =D5 | |
| E10 | =(D10)*1.1 | |
| E15 | =((D15)*(1+E14))*(1+E14) | |
| F5 | =E5 | |
| F10 | =(E10)*1.1 | |
| F15 | =((E15)*(1+F14))*(1+F14) | |
| G5 | =F5 | |
| G10 | =(F10)*1.1 | |
| G15 | =((F15)*(1+G14))*(1+G14) | |
| D9 | =1+D8 | |
| D14 | =1+D13 | |
In the first example, we have the pre-existing sales year on year for 4 years.
However, we realise we need to adjust the sales year on year by some growth rate. The second example is a first attempt which copies and special pastes by value the multiplier 110% into each of the future years. This changes all of their formula to be multiplied by 1.1.
But actually, what I want is to be able to paste a reference into the formula of each of the cells in row 9 (e.g. "*D9") so that I can change the growth rate for all of them easily by changing the value in the "Growth" cell.
Doing a special paste by formula doesn't work (see the third example, where the multiplier for cells E15:G15 isn't in the D column anymore), and while I can manually edit the cell formulas when there's only 4 of them it's harder when there's 4000.
Many thanks in advance for your help,
Christo
