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,153
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,153
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,081,441
Messages
5,358,694
Members
400,508
Latest member
fish31

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top