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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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,200
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,200
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,200
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,160
Messages
5,835,733
Members
430,383
Latest member
Kastore

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