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

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?

edited Mar 27 '16 at 22:32

fixer1234

18.8k144982

asked Feb 24 '15 at 20:53

xited

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

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

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?

edited Mar 27 '16 at 22:32

fixer1234

18.8k144982

asked Feb 24 '15 at 20:53

xited

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

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

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?

edited Mar 27 '16 at 22:32

fixer1234

18.8k144982

asked Feb 24 '15 at 20:53

xited

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

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

xited

1732513

edited Mar 27 '16 at 22:32

fixer1234

18.8k144982

asked Feb 24 '15 at 20:53

xited

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

asked Feb 24 '15 at 20:53

xited

1732513

asked Feb 24 '15 at 20:53

xited

1732513

asked Feb 24 '15 at 20:53

xited

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

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

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_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

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 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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

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 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 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 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 Student

13.7k31730

answered Feb 25 '15 at 18:35

Gary's Student

13.7k31730

answered Feb 25 '15 at 18:35

Gary's Student

13.7k31730

answered Feb 25 '15 at 18:35

Gary's Student

13.7k31730

add a comment |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Csdrhrt