Dynamic updating field

[burrin.p]

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

 

[burrin.p]

did a quick search and found this link posted in the forums here

http://www.ozgrid.com/forum/showthread.php?t=62478

i don't understand it but i have a feeling it's similar to what i'm looking for

if there's an easier way it would be appreciated.

thanks,
peter

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H	I
1	x	1	2	3	4	5		Square Root
2	1	1	2	3	4	5		1.414213562
3	2	2	4	6	8	10
4	3	3	6	9	12	15
5	4	4	8	12	16	20
6	5	5	10	15	20	25
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]

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]

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]

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]

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