Formula to update cell based on user changing another cell's content
I want to know if there is a formula to update a cell in Excel with today's date
- when a user changes any value in the row that contains that cell
OR
- when a user changes the value of 6 / 7 / 8 specific cells (which happen to be on the same row).
Getting today's date is fairly easy using A1 = today(), but how do I get this to happen on user update as described above?
microsoft-excel worksheet-function date-time
edited Mar 27 '16 at 22:32
fixer1234
18.8k144982
asked Feb 24 '15 at 20:53
xitedxited
1732513
add a comment |
I want to know if there is a formula to update a cell in Excel with today's date
- when a user changes any value in the row that contains that cell
OR
- when a user changes the value of 6 / 7 / 8 specific cells (which happen to be on the same row).
Getting today's date is fairly easy using A1 = today(), but how do I get this to happen on user update as described above?
microsoft-excel worksheet-function date-time
edited Mar 27 '16 at 22:32
fixer1234
18.8k144982
asked Feb 24 '15 at 20:53
xitedxited
1732513
Is the workbook set to automatic calculation?
– CharlieRB
Feb 24 '15 at 20:56
no. Could I use that to detect a cell's content change?
– xited
Feb 24 '15 at 20:59
You will need to incorporate VBA for this. Excel is set to automatically update everything anytime any cell changes. The only way around this is to set calculation mode to manual. Next you need a trigger event to trigger the manual update other than clicking the update button. This is where you will need VBA to create such a trigger. Would you like help writing such an event or are you against using VBA?
– Eric F
Feb 24 '15 at 21:03
I think it'd be easier to drop the formula altogether and just use VBA to set the static value in the cell. It sounds like you want a "date updated" field or something. That's not too hard if you're familiar with VBA. TheWorksheet_Changeevent can act as the trigger. UseINTERSECTto detect if they've changed cells you care about and then run the code to update the date value. Take a stab at that and come back with questions.
– Engineer Toast
Feb 24 '15 at 21:46
Before you spend a lot of time on formula or VBA, turn on automatic calculation to see if that solves this for you.
– CharlieRB
Feb 25 '15 at 16:32
add a comment |
I want to know if there is a formula to update a cell in Excel with today's date
- when a user changes any value in the row that contains that cell
OR
- when a user changes the value of 6 / 7 / 8 specific cells (which happen to be on the same row).
Getting today's date is fairly easy using A1 = today(), but how do I get this to happen on user update as described above?
microsoft-excel worksheet-function date-time
edited Mar 27 '16 at 22:32
fixer1234
18.8k144982
asked Feb 24 '15 at 20:53
xitedxited
1732513
I want to know if there is a formula to update a cell in Excel with today's date
- when a user changes any value in the row that contains that cell
OR
- when a user changes the value of 6 / 7 / 8 specific cells (which happen to be on the same row).
Getting today's date is fairly easy using A1 = today(), but how do I get this to happen on user update as described above?
microsoft-excel worksheet-function date-time
microsoft-excel worksheet-function date-time
edited Mar 27 '16 at 22:32
fixer1234
18.8k144982
asked Feb 24 '15 at 20:53
xitedxited
1732513
edited Mar 27 '16 at 22:32
fixer1234
18.8k144982
asked Feb 24 '15 at 20:53
xitedxited
1732513
edited Mar 27 '16 at 22:32
fixer1234
18.8k144982
edited Mar 27 '16 at 22:32
fixer1234
18.8k144982
edited Mar 27 '16 at 22:32
fixer1234
18.8k144982
18.8k144982
asked Feb 24 '15 at 20:53
xitedxited
1732513
asked Feb 24 '15 at 20:53
xitedxited
1732513
asked Feb 24 '15 at 20:53
xitedxited
1732513
1732513
Is the workbook set to automatic calculation?
– CharlieRB
Feb 24 '15 at 20:56
no. Could I use that to detect a cell's content change?
– xited
Feb 24 '15 at 20:59
You will need to incorporate VBA for this. Excel is set to automatically update everything anytime any cell changes. The only way around this is to set calculation mode to manual. Next you need a trigger event to trigger the manual update other than clicking the update button. This is where you will need VBA to create such a trigger. Would you like help writing such an event or are you against using VBA?
– Eric F
Feb 24 '15 at 21:03
I think it'd be easier to drop the formula altogether and just use VBA to set the static value in the cell. It sounds like you want a "date updated" field or something. That's not too hard if you're familiar with VBA. TheWorksheet_Changeevent can act as the trigger. UseINTERSECTto detect if they've changed cells you care about and then run the code to update the date value. Take a stab at that and come back with questions.
– Engineer Toast
Feb 24 '15 at 21:46
Before you spend a lot of time on formula or VBA, turn on automatic calculation to see if that solves this for you.
– CharlieRB
Feb 25 '15 at 16:32
add a comment |
Is the workbook set to automatic calculation?
– CharlieRB
Feb 24 '15 at 20:56
no. Could I use that to detect a cell's content change?
– xited
Feb 24 '15 at 20:59
You will need to incorporate VBA for this. Excel is set to automatically update everything anytime any cell changes. The only way around this is to set calculation mode to manual. Next you need a trigger event to trigger the manual update other than clicking the update button. This is where you will need VBA to create such a trigger. Would you like help writing such an event or are you against using VBA?
– Eric F
Feb 24 '15 at 21:03
I think it'd be easier to drop the formula altogether and just use VBA to set the static value in the cell. It sounds like you want a "date updated" field or something. That's not too hard if you're familiar with VBA. TheWorksheet_Changeevent can act as the trigger. UseINTERSECTto detect if they've changed cells you care about and then run the code to update the date value. Take a stab at that and come back with questions.
– Engineer Toast
Feb 24 '15 at 21:46
Before you spend a lot of time on formula or VBA, turn on automatic calculation to see if that solves this for you.
– CharlieRB
Feb 25 '15 at 16:32
Is the workbook set to automatic calculation?
– CharlieRB
Feb 24 '15 at 20:56
Is the workbook set to automatic calculation?
– CharlieRB
Feb 24 '15 at 20:56
no. Could I use that to detect a cell's content change?
– xited
Feb 24 '15 at 20:59
no. Could I use that to detect a cell's content change?
– xited
Feb 24 '15 at 20:59
You will need to incorporate VBA for this. Excel is set to automatically update everything anytime any cell changes. The only way around this is to set calculation mode to manual. Next you need a trigger event to trigger the manual update other than clicking the update button. This is where you will need VBA to create such a trigger. Would you like help writing such an event or are you against using VBA?
– Eric F
Feb 24 '15 at 21:03
You will need to incorporate VBA for this. Excel is set to automatically update everything anytime any cell changes. The only way around this is to set calculation mode to manual. Next you need a trigger event to trigger the manual update other than clicking the update button. This is where you will need VBA to create such a trigger. Would you like help writing such an event or are you against using VBA?
– Eric F
Feb 24 '15 at 21:03
I think it'd be easier to drop the formula altogether and just use VBA to set the static value in the cell. It sounds like you want a "date updated" field or something. That's not too hard if you're familiar with VBA. The
Worksheet_Change event can act as the trigger. Use INTERSECT to detect if they've changed cells you care about and then run the code to update the date value. Take a stab at that and come back with questions.– Engineer Toast
Feb 24 '15 at 21:46
I think it'd be easier to drop the formula altogether and just use VBA to set the static value in the cell. It sounds like you want a "date updated" field or something. That's not too hard if you're familiar with VBA. The
Worksheet_Change event can act as the trigger. Use INTERSECT to detect if they've changed cells you care about and then run the code to update the date value. Take a stab at that and come back with questions.– Engineer Toast
Feb 24 '15 at 21:46
Before you spend a lot of time on formula or VBA, turn on automatic calculation to see if that solves this for you.
– CharlieRB
Feb 25 '15 at 16:32
Before you spend a lot of time on formula or VBA, turn on automatic calculation to see if that solves this for you.
– CharlieRB
Feb 25 '15 at 16:32
add a comment |
1 Answer
1
active
oldest
votes
This is an example for row#7. Enter the following event macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Set R = Range("A7").EntireRow
If Intersect(R, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
R.Cells(1, 1).Value = Date
Application.EnableEvents = True
End Sub
If anything is changed in row#7, A7 will be updated with today's date.
Because it is worksheet code, it is very easy to install and automatic to use:
- right-click the tab name near the bottom of the Excel window
- select View Code - this brings up a VBE window
- paste the stuff in and close the VBE window
If you have any concerns, first try it on a trial worksheet.
If you save the workbook, the macro will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the macro:
- bring up the VBE windows as above
- clear the code out
- close the VBE window
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
To learn more about Event Macros (worksheet code), see:
http://www.mvps.org/dmcritchie/excel/event.htm
Macros must be enabled for this to work!
answered Feb 25 '15 at 18:35
Gary's StudentGary's Student
13.7k31730
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f882132%2fformula-to-update-cell-based-on-user-changing-another-cells-content%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This is an example for row#7. Enter the following event macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Set R = Range("A7").EntireRow
If Intersect(R, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
R.Cells(1, 1).Value = Date
Application.EnableEvents = True
End Sub
If anything is changed in row#7, A7 will be updated with today's date.
Because it is worksheet code, it is very easy to install and automatic to use:
- right-click the tab name near the bottom of the Excel window
- select View Code - this brings up a VBE window
- paste the stuff in and close the VBE window
If you have any concerns, first try it on a trial worksheet.
If you save the workbook, the macro will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the macro:
- bring up the VBE windows as above
- clear the code out
- close the VBE window
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
To learn more about Event Macros (worksheet code), see:
http://www.mvps.org/dmcritchie/excel/event.htm
Macros must be enabled for this to work!
answered Feb 25 '15 at 18:35
Gary's StudentGary's Student
13.7k31730
add a comment |
This is an example for row#7. Enter the following event macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Set R = Range("A7").EntireRow
If Intersect(R, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
R.Cells(1, 1).Value = Date
Application.EnableEvents = True
End Sub
If anything is changed in row#7, A7 will be updated with today's date.
Because it is worksheet code, it is very easy to install and automatic to use:
- right-click the tab name near the bottom of the Excel window
- select View Code - this brings up a VBE window
- paste the stuff in and close the VBE window
If you have any concerns, first try it on a trial worksheet.
If you save the workbook, the macro will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the macro:
- bring up the VBE windows as above
- clear the code out
- close the VBE window
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
To learn more about Event Macros (worksheet code), see:
http://www.mvps.org/dmcritchie/excel/event.htm
Macros must be enabled for this to work!
answered Feb 25 '15 at 18:35
Gary's StudentGary's Student
13.7k31730
add a comment |
This is an example for row#7. Enter the following event macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Set R = Range("A7").EntireRow
If Intersect(R, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
R.Cells(1, 1).Value = Date
Application.EnableEvents = True
End Sub
If anything is changed in row#7, A7 will be updated with today's date.
Because it is worksheet code, it is very easy to install and automatic to use:
- right-click the tab name near the bottom of the Excel window
- select View Code - this brings up a VBE window
- paste the stuff in and close the VBE window
If you have any concerns, first try it on a trial worksheet.
If you save the workbook, the macro will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the macro:
- bring up the VBE windows as above
- clear the code out
- close the VBE window
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
To learn more about Event Macros (worksheet code), see:
http://www.mvps.org/dmcritchie/excel/event.htm
Macros must be enabled for this to work!
answered Feb 25 '15 at 18:35
Gary's StudentGary's Student
13.7k31730
This is an example for row#7. Enter the following event macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Set R = Range("A7").EntireRow
If Intersect(R, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
R.Cells(1, 1).Value = Date
Application.EnableEvents = True
End Sub
If anything is changed in row#7, A7 will be updated with today's date.
Because it is worksheet code, it is very easy to install and automatic to use:
- right-click the tab name near the bottom of the Excel window
- select View Code - this brings up a VBE window
- paste the stuff in and close the VBE window
If you have any concerns, first try it on a trial worksheet.
If you save the workbook, the macro will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the macro:
- bring up the VBE windows as above
- clear the code out
- close the VBE window
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
To learn more about Event Macros (worksheet code), see:
http://www.mvps.org/dmcritchie/excel/event.htm
Macros must be enabled for this to work!
answered Feb 25 '15 at 18:35
Gary's StudentGary's Student
13.7k31730
answered Feb 25 '15 at 18:35
Gary's StudentGary's Student
13.7k31730
answered Feb 25 '15 at 18:35
Gary's StudentGary's Student
13.7k31730
answered Feb 25 '15 at 18:35
Gary's StudentGary's Student
13.7k31730
13.7k31730
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f882132%2fformula-to-update-cell-based-on-user-changing-another-cells-content%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Is the workbook set to automatic calculation?
– CharlieRB
Feb 24 '15 at 20:56
no. Could I use that to detect a cell's content change?
– xited
Feb 24 '15 at 20:59
You will need to incorporate VBA for this. Excel is set to automatically update everything anytime any cell changes. The only way around this is to set calculation mode to manual. Next you need a trigger event to trigger the manual update other than clicking the update button. This is where you will need VBA to create such a trigger. Would you like help writing such an event or are you against using VBA?
– Eric F
Feb 24 '15 at 21:03
I think it'd be easier to drop the formula altogether and just use VBA to set the static value in the cell. It sounds like you want a "date updated" field or something. That's not too hard if you're familiar with VBA. The
Worksheet_Changeevent can act as the trigger. UseINTERSECTto detect if they've changed cells you care about and then run the code to update the date value. Take a stab at that and come back with questions.– Engineer Toast
Feb 24 '15 at 21:46
Before you spend a lot of time on formula or VBA, turn on automatic calculation to see if that solves this for you.
– CharlieRB
Feb 25 '15 at 16:32