# Value on dependent cell driven by timestamp of most recent input

#### rupertlo

##### Board Regular
(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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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?

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

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

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

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

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

what code do you have in spin button control ?

None (I think)

Private Sub SpinButton1_Change()

End Sub

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

(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.

Replies
2
Views
317
Replies
8
Views
247
Replies
4
Views
164
L
Replies
8
Views
355
Legacy 352679
L
Replies
2
Views
305

1,214,262
Messages
6,118,551
Members
448,835
Latest member
Profast123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back