Remove the Formula but leave the value alone?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paic101 - 08 Jul 2007 01:54 GMT

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 can’t 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(F2>B1,C4+D4+E4,F4))
G4: =IF(G2=B1,VLOOKUP($C2,'Sales07'!$B:$Z,10,FALSE),IF(G2>B1,D4+E4+F4,G4))
H4: =IF(H2=B1,VLOOKUP($C2,'Sales07'!$B:$Z,10,FALSE),IF(H2>B1,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 haven’t 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.

Reply to this Message

joeu2004 - 08 Jul 2007 03:47 GMT

> 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.

Reply to this Message

paic101 - 08 Jul 2007 17:42 GMT

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?

> > 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
[quoted text clipped - 10 lines]
> Simply select the cell with the formula to be replace, right-click
> Copy, then right-click Paste Special and select Values.

Reply to this Message