Dynamic updating field

burrin.p

New Member
Joined
May 8, 2009
Messages
5
Hi everyone;
I don't play with excel a lot but thought i'd ask if it was possible to have one field remain as dynamic / active no matter what 'other' field is clicked on / selected.
so, i have a times table that i made and i was thinking that it would also be good to have one field show / provide the square root of whatever other field was clicked on
so, if i clicked on the interection of 5 x 20 (100) then there would be another field say on the left or bottom that was designated as the square root and showed the answer 10 as the square root of 100 (10).
is there such a thing in excel as this sort of dynamic updating for one specific (or more) field ??
perhaps even just a mouse-over option instead of actually clicking on a field to show the square root in the other field.
Thank you,
peter
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,633
Office Version
365
Platform
Windows
Peter

Welcome to the MrExcel board!

See if this suits. My setup is shown below (result with cell C2 selected). You might want to test on a setup like this then adapt to your own.

Excel Workbook
ABCDEFGHI
1x12345Square Root
21123451.414213562
32246810
433691215
5448121620
65510152025
7
Result Based on Selection



Steps to implement:

1. Right click the sheet name tab and choose 'View Code'.

2. Copy the code below and Paste into the main right hand pane that opens at step 1.

3. Close the VB window and try selecting cells in (or out of) the table range.


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> AC <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">Set</SPAN> AC = Range("H2") <SPAN style="color:#007F00">'<-- Answer cell</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Selection.Count > 1 <SPAN style="color:#00007F">Then</SPAN><br>        AC.ClearContents<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, Range("B2:F6")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        AC.ClearContents<br>    <SPAN style="color:#00007F">Else</SPAN><br>        AC.Value = Sqr(Target.Value)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 

burrin.p

New Member
Joined
May 8, 2009
Messages
5
Thank you very much.
That worked perfectly.
I just changed the answer field and the range values and it was perfect right away.
Thank you ... now i'll have to go over it a little to see if i can understand what's going on in it.
Very much appreciated - have a young student i'm tutoring which is why I did the times table so it'll be a help to have the square roots right there too.
All the best,
Peter
 

burrin.p

New Member
Joined
May 8, 2009
Messages
5
K ... so I thought that I would experiment and see if I could figure out how to do cube root also

I searched around and found a cuberoot formula for it and trial & errored my way into getting it to function.

here's what i have now (works with / without the "Dim BC As Range" statement:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim AC As Range
Dim BC As Range

Set AC = Range("W3") '<-- SquareRoot Answer cell
Set BC = Range("X3") '<-- CubeRoot Answer cell
If Selection.Count > 1 Then
AC.ClearContents
BC.ClearContents
Exit Sub
End If
If Intersect(Target, Range("C3:V22")) Is Nothing Then
AC.ClearContents
BC.ClearContents
Else
AC.Value = Sqr(Target.Value)
BC.Value = Exp(Log(Target.Value) / 3#)
End If
End Sub

Could you look it over and see if you can spot any problems with it that I might not be aware of?

Thank you,
Peter
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,633
Office Version
365
Platform
Windows
Could you look it over and see if you can spot any problems with it that I might not be aware of?
Not a problem as such, but a bit simpler:
BC.Value = Target.Value ^ (1 / 3)
 

burrin.p

New Member
Joined
May 8, 2009
Messages
5
Thank you very much - definitely simpler.
I appreciate your expertise.
I think it would be worthwhile for me to start to learn about VB as it seems to be a useful resource.
Could you / anyone suggest a good beginner resource to start studying / reading?
Thank you,
Peter
 

Forum statistics

Threads
1,085,352
Messages
5,383,114
Members
401,815
Latest member
nube78

Some videos you may like

This Week's Hot Topics

Top