Remove the Formula but leave the value alone?
 

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.

Remove the Formula but leave the value alone?
 

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.

Remove the Formula but leave the value alone?
 

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.