Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 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
Sapphire Radeon X1800XT 256MB
Corsair XMS2 TWIN2X 2 x 1GB RAM
Antec TruePower 2.0 TP-II 550 Watt
Reply With Quote
  #2  
Old 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
Reply With Quote
  #3  
Old 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
Sapphire Radeon X1800XT 256MB
Corsair XMS2 TWIN2X 2 x 1GB RAM
Antec TruePower 2.0 TP-II 550 Watt
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Need help converting formula into value in Excel"
Thread Thread Starter Forum Replies Last Post
Need formula help for MS Excel RlyPlyPanda MS Office Support 1 08-05-2012 01:46 PM
Excel Formula Scott2580 MS Office Support 2 15-02-2011 02:21 PM
Excel formula Me Me Taylor Windows Software 2 06-04-2009 04:11 PM
Formula help in excel 2007 Muhammad Waqar Windows Software 1 24-11-2008 07:29 PM
Need help in excel formula Landon Software Development 2 27-10-2008 03:28 PM


All times are GMT +5.5. The time now is 02:40 AM.