Calculating the value of a cell from another cell

[GPark1018]

I am interesting in how I would go about creating a value for a cell
by referencing it from another cell. I know this logic doesn't work
in Excel, but it makes sense to me...

(Function connected with cell C2) IF(C2>1,C3=1,C3="")

If this worked then the user could also manually override the value in
C3.

If I placed the function above into the Cell C3 I loose the ability
for the user to hand-enter values into C3 without corrupting the
function.

Is this possible?

Thank you to anyone who may respond!

Greg

 

[Pete_UK]

Let's say that the user entered values into cell G3 and that these
took precedence over anything else. Then in C3 you could have the
formula:

=IF(G3<>"",G3,IF(C2>1,1,""))

This way your formula in C3 is intact (and could be protected to
prevent it being over-written), but your user still has the ability to
manually specify another value to be used.

Hope this helps.

Pete

 

[Gord Dibben]

You cannot push a value from C2 to C3, you must pull from C2 to C3 by using
a formula in C3

Cells can have a formula or a manually entered value, not both.

You could employ event code to place a value in C3 based upon value in C2

You could then override that value in C3 by manual entry so you could have
either value.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C2")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Range("C3")
Select Case Target.Value
Case 1
.Value = Target.Value
Case Else
.Value = ""
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-clcik on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

Then Alt + q to return to Excel window.

Type a number in C2 and see C3 either 1 or nothing

Type a number in C3 to see that number.


Gord Dibben MS Excel MVP