# Excel: link value once, then prevent change

For some budgeting spreadsheets I'm working on, I'd like to link each month to a value (in this case, a percentage). However, if the original percentage is changed I ONLY want to change values going forward.

For example, let's say item one is budgeted at 10%, so each month reflects 10% of the total (which changes every month). If I decide to change that to 12% going forward, I don't want the previously linked values to also change from 10 to 12% (and throw off lots of other numbers).

My thought was to have a check box where if I placed an x, the values would be locked to the value at the time of placing the x and no longer change.

Is this possible? I know there are options for doing a paste special, but I'm creating this spreadsheet for someone who is not very Excel savvy, so I want it to be as seamless as possible.

Many thanks.

-

## migrated from stackoverflow.comNov 18 '12 at 17:01

This question came from our site for professional and enthusiast programmers.

You could just use an `IF` statements and check on date.

Example: I want to use 10% for dates Jan and Feb and then 12% thereafter

``````    Date      %
1   Jan-12    10  =IF(A1<\$A\$3, 0.1, 0.12)
2   Feb-12    10  =IF(A2<\$A\$3, 0.1, 0.12)
3   Mar-12    12  =IF(A3<\$A\$3, 0.1, 0.12)
``````

I am not sure this gives you exactly what you need but a variation on this theme should get you moving ahead.

-

You could use a lookup table with a rate for each month where there's a change:

Here, the lookup table is to the left and the values looked up to the right. You'll notice that I didn't enter a rate for April and the April entries are still picking up the rate for March. This is similar to a table a teacher uses to look up grades by range. It relies on `VLOOKUP's TRUE` argument, which says find the nearest match that's smaller in an ordered list.

The formula for the VLookup, in E2 in this example is:

=VLOOKUP(D2,\$A\$2:\$B\$4,2,TRUE)

-
Hmm. That actually might work. I've never used VLookup, but this seems like a good time to learn. I'll check it out and see what I come up with. Thanks! – user1832164 Nov 17 '12 at 21:13

For “someone who is not very Excel savvy” I’d recommend keeping things as simple as possible and suggest one way to achieve this is to ‘declare’ the percentage and calculate the results merely as `% times value`.

Since dealing in months a ‘regular’ 10% seems a little odd because at 10% per month a year’s worth is 120% rather than the 100% I would have expected. However, using that as a starting point, the results for a value of 100 would be as ‘Prediction’ in example. Here `B3` is hard coded as 10% but `C3` is `=B3` and `D3:M3` just copied across from `C3`.

But the sequence can be broken where required (in the example, in May) by overwriting the formula in `F8` (ie `=E8`) with `12%`. The formula in `G8` remains as `=F8` so 12% will apply for June onwards also (unless later hard coded again).

-