Prevent cell update if valued

[WDGOR]

I have a sheet (Summary Sheet) that uses formulas to fill in values from an updateable data sheet (Pipeline). I am trying to prevent updating the values in the Summary Sheet if a value already exists. As an example, the formula below looks for the range of dates from 1/1/2014 to 12/31/2014 and returns a value. The Pipeline sheet is updated monthly. If a value is posted for that month (January in this example), even if it is zero, it should not change that January value when the Pipeline sheet is updated in February, i.e. even if the January value is changed in February on the Pipeline sheet, the original value on the Summary Sheet from January needs to be maintained since it was entered in January. There is a large range of cells that need to be checked and prevented from an update if it has already been run for that month.

=IF($B4="RSD",(SUMIFS(Pipeline!$I$2:$I$5000,Pipeline!$AG$2:$AG$5000,"CVIS",Pipeline!$AF$2:$AF$5000,"0",Pipeline!$AL$2:$AL$5000,$A4,Pipeline!$F$2:$F$5000,">="&"1/1/2014",Pipeline!$F$2:$F$5000,"<="&"12/31/2014")),0)

Thank you for any assistance. I anticipate that this will require VBA code and I am not proficient in that element.

[6StringJazzer]

...the formula below looks for the range of dates from 1/1/2014 to 12/31/2014 and returns a value. The Pipeline sheet is updated monthly.

I may not be understanding your question, but I don't see why you are looking for a range of dates for the whole year if you are trying to capture a figure for January.

[WDGOR]

Sales wants to look at a rolling 12 months but once that is determined in any particular month, it needs to be maintained for that month even if they go in and make changes, i.e. we want to maintain the original estimate. Probably the simplest thing to do would be to prevent updates in the source but they want the flexibility to make changes anytime. I just need to see if there is a way that I can tell Excel that I already have a value in that month so don't update that cell. Does that help clarify it better?

[6StringJazzer]

Yes. There are a couple of ways to do this, and every way I can think of requires VBA. The first one that comes to mind is a macro that will allow you to double-click on a cell containing a formula, and turn it into a value.

Another similar way would be to add a button so that when you click the button, the active cell is converted from a formula to a value.

Attached is a sample file illustrating both methods. In each case, the action will be ignored if more than one cell is selected.

[WDGOR]

Jeff,

Thanks for the prompt response. I tried your solution and it works. My issue is that I don't want to change the underlying formula but just not update the value if there is already a value present for that month or any previous month that already has a value. I also have hundreds of cells where this condition would need to be applied. What I want may not be as "easy" to implement as I had anticipated.

[6StringJazzer]

It's not possible to freeze the result of a formula and retain the underlying formula.

[WDGOR]

Bummer - I was afraid of that. Thanks a lot for looking at the issue and the advice. Maybe Excel 2020 will do it.

[6StringJazzer]

Yes, I have heard that the "have your cake and eat it too" feature is planned for 2020