Excel =IF? Closed

Question

Im trying to put a =IF function in one cell to refer to another cell. After that i want it to post the results in yet another cell. If that makes sense. For example:

In A1: =IF(A2=1) Then i want A3 to have 10 added to it

I tried =IF(A2=1,THEN(A3=+10)) But this doesnt work.

Reason im doing this is because i have another base number in "A3" so i cant put the formula there. But i want to use whats currently in A3 and A2 to calculate a new A3.

Any suggestions?

TrowaD · Answer

Hi MitchW,  

A formula won't do the job. A formula can't change the value of another cell.  
A VB code can.  

So try this code:  
Sub test() 
If Range("A2").Value = 1 Then 
Range("A3").Value = Range("A3").Value + 10 
    End If 
End Sub 
Best regards, 
Trowa

hackimist · Answer

Nope i don't have any suggestions If my calculation is correct . . . 

i don't think that's possible,trying to use =IF to refer something is possible but you also want it to post itself automatically in YET another cell?

I've never encounter a calculation like that. . . . 

But i want to use whats currently in A3 and A2 to calculate a new A3?

It's like deleting a file that is in use.

But good luck maybe there's someone who knows about stuff

TrowaD · Answer

No problem dude, we all started out as noobs.

There are different possibilities to run a code.
Let's take a look at the most common one.

To manually run the code implement the code as follows:
In excel hit Alt+F11 to open the VB editor.
Now goto the top menu's Insert > Module.
Copy/paste the code in the big empty white field.
The word behind Sub in the first line of code is the name of the code.
In this case it's test, but this can be changed to whatever one word you like.
After hitting Alt+F8 the name of the macro will be displayed and can be executed.
VB editor doesn't need to stay open for you to run the code.

Hopefully this will help you on your way.

Best regards,
Trowa

MitchW · Answer

Yes the code does work. Now is it possible to have the program run just after i input a new number into A3?

Like the number was previously 10. running the script added 10 to a total of 20. now i want to change the original bast to 15 and rerun the script automatically.

TrowaD · Answer

Hi MitchW,

What you are asking now can't be done imo.

You want the code to run when a change is made; no problem.
But think about what is happening:
You make change to A3,
this will call the code making a change to A3,
since a change has been made the code will run again making a change to A3, etc.

Let me show you some options.
When in VB editor, on the left side, above the inserted module, your sheets are visible, double click on the sheet which is in effect. You can also get here by right clicking on the sheets tab in excel and selecting the bottom option called "View code".
On top of the big white field you will see two dropdown boxes.
Click on the left one first and select worksheet.
Now when you click on the right one you will see a bunch of events which will make it possible to run your code automatically.
This might help you think of a solution.

Best regards,
Trowa

Excel =IF?

5 responses

Similar discussions

Subscribe To Our Newsletter!