Formula to Avoid Circular Reference

[Janet Henry]

I want to make an entry in Cell A1 but I want it multiplied by an absolute
reference in another cell. How can I avoid a circular reference? For
example, how can I insert a 10 in A1 and multiply it times the absolute
reference to 75 and have the answer be 750 when I click out of the cell? I
am open to an IF function in another cell but do not know how to designate
the location of the results. Thanks.

A B C


75

 

[JLatham]

A cell can have either a formula or a value, not both. So you can't have a
"self-modifying" cell. What you need to do is set up another cell where you
enter the variable value (10 in your example).
For example's sake, lets say this input cell is at B99 and the fixed value
of 75 is in F100, then in A1 you could have this formula:
=B99 * F100
As you type values into B99, A1 will change to recalculate its value.

 

[Janet Henry]

Thanks so much for taking your time out for me, but that will not work for
this situation. Your suggestion gave me something to think about though. We
have inserted a macro but a box comes up each time and we just want to insert
the number and it calculate automatically. I will keep thinking. Thanks
again.

 

[JLatham]

Not sure how you've created and set up your macro. But you should be able to
use an InputBox() function to get some input from the user and multiply that
by the 75 and put the result into A1. Probably want to associate all of this
with the worksheet's _SelectionChange() event and test to see if A1 was the
cell just clicked in.

 

[Janet Henry]

The idea of a function would be great. Could you tell me the arguments for
the InputBox function? There is no help in Excel for it. Thanks!

 

[JLatham]

Janet, below is sample code to show how it can be implemented. This is a
worksheet event handler, so it needs to go into the worksheet's code module.
To get it there, go to the sheet in question and right-click on the sheet's
name tab and choose [View Code] from the popup list. Copy the code below and
paste it into the module presented to you then close the VB Editor.

After that, each time you click in cell A1 on that sheet (with macros
enabled), you'll be asked to provide some input, with the default being zero.
The code is set up so that if you enter zero, previous entry in A1 is not
changed. But any non-zero value would cause it to be recalculated.

The InputBox() function takes several parameters, I've just used the 1st 3:
Prompt - the message to the user to ask for the input
Title - title placed on the input box itself
Default value - a value to return if the user doesn't enter anything, zero
in this case

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim userInput As String ' InputBox gets a string value

If Target.Address <> "$A$1" Then
Exit Sub ' nothing to do, ignore
End If
userInput = InputBox("Please Enter a numeric value", "Title", 0)
'coerce string input to numeric value
'if the user entered zero or just hit enter,
' then don't change value in A1
If Val(userInput) <> 0 Then
' G1 has constant value of 75, change as needed
Range("A1") = Val(userInput) * Range("G1")
End If
End Sub