How easy a macro can be developed to remove the formula but leave the value
alone for a group of cells when a special condition matches like my example
here?
I started with the formula below but realized that I cant get the result I
wanted as to leave the value alone for F4 in the IF formula when the month
moving forward. The F4 value originally returned from the Vlookup result.

F4: =IF(F2=B1,VLOOKUP($C2,'Sales07'!$B:$Z,10,FALSE),IF (F2B1,C4+D4+E4,F4))
G4: =IF(G2=B1,VLOOKUP($C2,'Sales07'!$B:$Z,10,FALSE),IF (G2B1,D4+E4+F4,G4))
H4: =IF(H2=B1,VLOOKUP($C2,'Sales07'!$B:$Z,10,FALSE),IF (H2B1,E4+F4+G4,H4))

Where B1 : 200706, F2 : 200706, G2 : 200707, H2 : 200708
Other the problem of circulated reference, when I changed B1 to 200707, the
calculation for G4 & H4 stopped working unless I manually reset F4 to be the
value from the lookup result.

I havent written VBA in Excel but it sounds like it would solve my problem,
right? Can someone help me with the solution or suggestion to do? Thanks.

On Jul 7, 5:54 pm, paic101 wrote:
How easy a macro can be developed to remove the formula but leave the value
alone for a group of cells when a special condition matches like my example
here?

I confess I really do not understand the problem you are trying to
solve, specifically if this a one-time thing or repeated periodically,
and if this impacts only one cell or more. I found your explanation
confusing.

But I wonder if you need a macro at all. Would the following suit
your purposes?

Simply select the cell with the formula to be replace, right-click
Copy, then right-click Paste Special and select Values.

Yes, I should specify that it's a monthly job as users now need to manually
retrieve data from different worksheet then copy/paste for several hundred
cells per spreadsheet. Plus those cells are spread across different rows
within many different columns, it's time consuming as they can't do the a
group copy/paste at once...That's why I tried to see if we can setup the
Vlookup formula for all months in advance. It would solve the problem except
I couldn't 'Do thing' within the IF formula for past months. With the
limitation of the IF formula, I like to know what and how to solve this
problem. Any more suggestions?

"joeu2004" wrote:

On Jul 7, 5:54 pm, paic101 wrote:
How easy a macro can be developed to remove the formula but leave the value
alone for a group of cells when a special condition matches like my example
here?

I confess I really do not understand the problem you are trying to
solve, specifically if this a one-time thing or repeated periodically,
and if this impacts only one cell or more. I found your explanation
confusing.

But I wonder if you need a macro at all. Would the following suit
your purposes?

Simply select the cell with the formula to be replace, right-click
Copy, then right-click Paste Special and select Values.