EXCEL formula pls help!

March 19, 2009 at 07:38:27
Specs: Windows Vista
Im trying to figure out how to write the blw excel formula. Anyone can help?

For eg,

If user enter cell A1 = 60 then cell A2 will auto reflect 40 and A3 will auto sum up A1 +A2 =100.

In return if user enter cell A2 = 60 then cell A1 will auto reflect 40 and A3 will auto sum up A1+A2 = 100 as well

Excel formula can support this ? Pls help.


See More: EXCEL formula pls help!

Report •


#1
March 19, 2009 at 08:22:52
Excel cannot have a formula in a cell and allow for user input directly into the same cell. If there is a formula in a cell, then it would get over-written when user enters 60.

There might be a way to use a Sheet_Change macro to do what you want, but you would need to be more specific.

For example, this would do what you asked. Right click the sheet tab, choose View code and paste this in the VBA window.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$1" Then
  If Target = 60 Then Range("A2") = 40
 End If
 
 If Target.Address = "$A$2" Then
  If Target = 60 Then Range("A1") = 40
 End If
End Sub


Report •

#2
March 20, 2009 at 20:03:09
how about if I want to apply until cell F ? how to make the it keep on looping ?

Report •

#3
March 23, 2009 at 18:19:00
re: how about if I want to apply until cell F ?

F is not a cell.

re:

how to make the it keep on looping ?

Looping through what?


Report •

Related Solutions

#4
March 28, 2009 at 19:19:21
mean that the formula will apply from cell A till cell Z

Report •

#5
March 28, 2009 at 19:33:25
A is not a cell, it's a column.

Z is not a cell, it too is a column.

I still don't know what you want to do. Please give us more detail.


Report •

#6
April 2, 2009 at 05:58:45
I mean all the formula must be apply to from cell A1 until cell Z1

Report •

#7
April 2, 2009 at 06:07:04
Re-read your original post.

- You wanted constants in A1 and A2
- You wanted those constants to change based on what was in each cell
- You wanted a formula in A3.

Now you say I mean all the formula must be apply to from cell A1 until cell Z1.

You never asked for a formula in A1 or A2 so I still have no idea what formula you want to apply to what cell.

Give us details like you did in your first post. Your one line responses are not helping.


Report •


Ask Question