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.

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

how about if I want to apply until cell F ? how to make the it keep on looping ?

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?

mean that the formula will apply from cell A till cell Z

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.

I mean all the formula must be apply to from cell A1 until cell Z1

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.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History