Result of formula in remote cell

[Andy Sandford]

Hi

I have a blank cell (K9) in which I need a user to enter data (a name). I
need a prompt ("Enter name") displayed in K9 which is the result of a
function in P33.

Currently K9 reads =P33, but I need the prompt to reappear if certain
things change elsewhere on the sheet. Obviously this won't work.

Is there any other way I can directly display the results of P33 in K9
without any code in K9?

Hope this makes sense!!

Andy

 

[Vasant Nanavati]

Not without using a macro.

 

[Andy Sandford]

Hi Vasant

I don't suppose you could elaborate! 8o))

Andy

 

[Vasant Nanavati]

Sure, Andy!

1. Right-click on your worksheet tab and click View Code.

2. Paste the following into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") = 1 Then 'a trivial example of a condition to be met
Range("P33") = Range("K9") 'or Range("P33") = "Enter name"
End If
End Sub

Does this help?