Value on dependent cell driven by timestamp of most recent input

rupertlo

Board Regular
Joined
Sep 10, 2014
Messages
53
(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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

rupertlo

Board Regular
Joined
Sep 10, 2014
Messages
53
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

Well-known Member
Joined
Oct 5, 2011
Messages
1,194
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
 

rupertlo

Board Regular
Joined
Sep 10, 2014
Messages
53
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
 

rupertlo

Board Regular
Joined
Sep 10, 2014
Messages
53

ADVERTISEMENT

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

Well-known Member
Joined
Oct 5, 2011
Messages
1,194
what code do you have in spin button control ?
 

rupertlo

Board Regular
Joined
Sep 10, 2014
Messages
53

ADVERTISEMENT

None (I think)

Private Sub SpinButton1_Change()

End Sub
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,194
How does spin button control change values of cell A1 and A2? Any macros assigned to it ?
 

rupertlo

Board Regular
Joined
Sep 10, 2014
Messages
53
(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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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?

Disable AdBlock

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
Top