TechArena Community Need help converting formula into value in Excel

#1
20-11-2008
 Member Join Date: Aug 2006 Posts: 267
Need help converting formula into value in Excel

Hi friends, I know the easiest way is to copy and use paste special then "value" options. but is there a way to make it automated in case the desired output value has resulted.

Quote:
 example: A1 B1 C1 D1 1 2 A1+B1 Yes/No
on the above example, if D1=Yes, the output C1 will become value equals to 3 and the formula will be remove, if D1=No, the formula will still remain.

Thanks in advance for any help
__________________
Intel E6300 Allendale 1.86 Ghz
GigaByte GA 965P DS3 LGA 775
Corsair XMS2 TWIN2X 2 x 1GB RAM
Antec TruePower 2.0 TP-II 550 Watt
#2
20-11-2008
 Member Join Date: Apr 2008 Posts: 2,001
Re: Need help converting formula into value in Excel

This can be done with a worksheet change event so whenever D1 changes the code will kick in

code:
Quote:
 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "\$D\$1" Then If Target = "Yes" Then Target.Offset(0, -1) = Range("A1") + Range("B1") Else Target.Offset(0, -1) = "=A1+B1" End If End If End Sub
This can only be done manually, like you said by copy and pasting special values or with VBA.

code:
Quote:
 Sub Button2_Click() If Range("D1") = "Yes" Then Range("C1") = Range("A1") + Range("B1") Else Range("C1") = "=A1+B1" End If End Sub
#3
20-11-2008
 Member Join Date: Aug 2006 Posts: 267
Re: Need help converting formula into value in Excel

Thanks kelfro. i tried both tricks it works.
__________________
Intel E6300 Allendale 1.86 Ghz
GigaByte GA 965P DS3 LGA 775
Corsair XMS2 TWIN2X 2 x 1GB RAM
Antec TruePower 2.0 TP-II 550 Watt

 Tags: