Value on dependent cell driven by timestamp of most recent input

[rupertlo]

(not sure I have got the right title, anyway here is the question...)


If I can do this via formulae, I would much prefer this to VBA

I have two calls with text

A1: Cat
A2: Dog

In Cell A3, I want to have the value of either A1 or A2, dependent on which one was most recently edited.

1. If A3 started by displaying "Cat"
2. I input "Mouse" in A2
3. A3 now shows "Mouse"
4. I input "Horse" in A1
5. A3 now shows "Horse"

Many thanks

 

[rupertlo]

I guess if there was a formula in cell B1 that produced a time stamp of the last time cell A1 was edited, that would do it. Is there such a formula?

 

[vds1]

Right click on the sheet tab name > Select View code > Paste below code

[COLOR=#0000ff]Private Sub[/COLOR] Worksheet_SelectionChange(ByVal Target As Range)
If [A1] <> [XFD1] [COLOR=#0000ff]Then[/COLOR] [A3] = [A1]
If [A2] <> [XFD2] [COLOR=#0000ff]Then[/COLOR] [A3] = [A2]
[XFD1] = [A1]
[XFD2] = [A2]
[COLOR=#0000ff]End Sub[/COLOR]

Now change A1 or A2

 

[rupertlo]

Right click on the sheet tab name > Select View code > Paste below code

[COLOR=#0000ff]Private Sub[/COLOR] Worksheet_SelectionChange(ByVal Target As Range)
If [A1] <> [XFD1] [COLOR=#0000ff]Then[/COLOR] [A3] = [A1]
If [A2] <> [XFD2] [COLOR=#0000ff]Then[/COLOR] [A3] = [A2]
[XFD1] = [A1]
[XFD2] = [A2]
[COLOR=#0000ff]End Sub[/COLOR]

Now change A1 or A2

vds1, thank you very much

 

[rupertlo]

Follow up question:

What happens if values of A1 and A2 are dependent on two separate spinbutton controls?

A3 will update when I click on another cell but will not adjust if I click the arrows on the spin button control

 

[vds1]

what code do you have in spin button control ?

 

[rupertlo]

None (I think)

Private Sub SpinButton1_Change()

End Sub

 

[vds1]

How does spin button control change values of cell A1 and A2? Any macros assigned to it ?

 

[rupertlo]

(I am sorry I cannot post attachments, otherwise I would post an example worksheet)

Spin control1 (which determines A1) is limited to a numerical range of 1 to 3. It outputs 1,2 or 3 to cell A8. The value in cell A8 is used in an offset function in A1 which reference a 3x1 range with text of Alpha, Bravo, Charlie). If the output of the spincontrol is 1 then A1 becomes Alpha. Output of 2, then A1 becomes Bravo. Output of 3, then A1 becomes Charlie.

Spin control2 (which determines A2) is limited to a numerical range of 1 to 3. It outputs 1,2 or 3 to cell A14. The value in cell A8 is used in an offset function in A2 which reference a 3x1 range with text of X-Ray, Yankee, Zulu. If the output of the spincontrol is 1 then A1 becomes X-Ray. Output of 2, then A1 becomes Yankee. Output of 3, then A1 becomes Zulu.

Thank you.