Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

"Last date updated" on spreadsheet

Hi,

Is it possible for Excel to register when a spreadsheet was last changed and then post that date in one of the cells?

I am using Excel 2003 and have a file with several sheets in it. Instead of correcting the date manually whenever I change something on a sheet, I would like Excel to do it for me, if it is possible.

Regards, LDKottal


Post your answer or comment

comments powered by Disqus
i have one share file, some range already fix the user to update the data. how can i show the data already updated by WHO and Date Updated on the top of the column.

for example:

<coloum a> <coloum b> <coloum c>
12/02/09 user a 13/02/09 user b 10/02/09 user c
data 1 data 2 data 3

I'm attempting to force excel to auto update the day of the week, and the date in a spreadsheet. The date isn't as important, since it can be hard coded. The only problem there is some months have 31 days, some 30, and another with 28. I've uploaded an image of the spreadsheet, and you can see in field A1 the date/year is input. I'm wanting to find a way to force the days/dates in fields 2E and 3E to update based on the month. I've searched google for a month of completing this task, as well as the forums, all with no luck. Any assistance would be greatly appreciated. A URL to the image is located here: http://www.pichub.com/show/card/5302 I apologize for the size. At work most website's are blocked. I got lucky in finding pichub. Thanks again.

Dear all;

This should be an easy one for you ; I work on spreadsheets daily and then
save them to refer to them later or update them. What I would like to achieve
is :

When I actually work on a spreadsheet - i want to insert the date of the day
only when the data changed or update the contents of the worksheet.

i mean what i am trying to achieve is insert the last date in a specefic
cell when that the file was modified (upadted-cahnge of contents)

Is there perhaps any way to do this
I thank you for your help and your offers

I think this might be a bit of a long shot as I'm not sure it's possible, but thought I would see if anyone on here could help!

I am trying to find a way to link two spreadsheets - one is a week long timetable and I would like the information from this to automatically update another spreadsheet, so that the people teaching can see a list of the dates relevant to them, but I think this would involve applying conditions that are a bit beyond me.

I've attached examples of the two sheets:

'Timetable' is the main spreadsheet which stores the information and is the one which the information will be typed into.
'Dates' is the spreadsheet which will be updated by 'Timetable' and has a tab for each person named on the timetable. I've laid out the first few tabs how I would ideally like them to look, although it's divided into 15 minute time slots and I'm not sure how that would work. Basically, each teacher's tab would be updated from the data stored in timetable, to give them an overview of what they alone would be teaching that week.

I'm not sure if that makes sense or if I've explained it very well, so please do ask if you have any questions.

Huge thanks in advance for any advice or help you can give.

I am working on an Excel spreadsheet and wanted to know if there is a function that will automatically display on the worksheet the date, time and name of last operator that used or updated the spreadsheet.

Also, are there are safeguards that can be put into place such as determining the wrong type, wrong sequence of entries, wrong data range, and wrong format of numbers entered?

Thank you.

Meg Wenstrand

Hi everybody,

I am working on a chart which is updated every week. For clearness purposes, I would like to get the chart titles integrating the date of the last update. For instance, if the data are updated on the 13/06/07, I would like the chart title to be automatically: Last updated on 13/06/07 and so on.

Thank you for your help.

Xavier

Can you please tell me the best way to achieve the desired result in my spreadsheet. I just want to find a way to get excel to return a value based on the last date entered.

I have a workbook that opens to sheet3 and in a cell on that sheet I would like to automatically show the last date entered from column A on sheet1 .

I have some code that shows the address in a message box but I'm so green when it comes to VBA that I don't know (yet) how to redirect the data in that address to a cell on sheet3


	VB:
	
 Range 
Set LastDate = Range("A1").End(xlDown).Offset(0, 0) 
MsgBox LastDate.Address 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for any help,

Hi,

I want to define a dynamic named range based on the last date in a range (AE4 down). Unfortunately there are gaps in this range where no date is in a cell. Is this possible?

Hello,

I am new to VBA and loving it, but so confused.
I have located through searching the code attached below on sending the email from excel and have updated the code with some input I needed.

I am confused on the IF Then statement that I need.
I want the spreadsheet to email the Supervisors and myself If/when a Expiration date is 10 days prior to due date(updates the spreadsheet in yellow) for an employee. Then email again when its 5 days prior to due date(cell turns red) and remains red until it is manually updated with the new date to stay red. I have set the colors up using conditional formatting but don't know how to do it with VBA coding? (found .Interior.ColorIndex = 3 but can't figure how to enter it)

When these dates (20days and 5 days out) trigger, need the emails to include the employee's first/last name and Item Expiring(like CDL License) with the date.

Many many thanks for any help. I hope I have followed the guidelines and explained it properly.


	VB:
	
 ShellExecute Lib "shell32.dll" _ 
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _ 
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _ 
ByVal nShowCmd As Long) As Long 
Sub SendEMail() 
    Dim Email As String, Subj As String 
    Dim Msg As String, URL As String 
    Dim r As Integer, x As Double 
    For r = 2 To 3 'data in rows 2-3
         '       Get the email address
        Email = Cells(r, 8) 
         
         '       Message subject
        Subj = "Upcoming Expiration Date(s)" 
         
         '       Compose the message
        Msg = "" 
         'Supervisor Name below
        Msg = Msg & "Dear " & Cells(r, 7) & "," & vbCrLf & vbCrLf 
        Msg = Msg & "The following employee has a due date set to expire on " 
         'Expiration Date
        Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf 
        Msg = Msg & "Troy Farmer" & vbCrLf 
        Msg = Msg & "HR-Safety Director" 
         
         '       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20") 
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20") 
         
         '       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") 
         '       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg 
         
         '       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus 
         
         '       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02")) 
        Application.SendKeys "%s" 
    Next r 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have two user forms the first form allows the user to add to the ws any job that needs to be held by the user. it includes date, jobname, etc... and end at column 9.

The second userform is for the user to update the spreadsheet, another words to release that same job that they put on hold earlier.

Here lies my problem:
I need the information that is inputted from userform 2 to be placed on the same row as the information from user form1 starting in column 10. So that when te user inputs the jobname on user form2 it will know what row to go to or if it's not within this database then msgbox " jobname not found"

How do I do this? I guess it would be some kind of a search but not sure since I have never done a search bfore in vba.

Any help with this matter would be GREATLY APPRECIATED.

thank you

Hello,

I am new to VBA and loving it, but so confused.
I have located through searching the code attached below on sending the email from excel and have updated the code with some input I needed.

I am confused on the IF Then statement that I need.
I want the spreadsheet to email the Supervisors and myself If/when a Expiration date is 10 days prior to due date(updates the spreadsheet in yellow) for an employee. Then email again when its 5 days prior to due date( cell turns red) and remains red until it is manually updated with the new date to stay red. I have set the colors up using conditional formatting but don't know how to do it with VBA coding? (found .Interior.ColorIndex = 3 but can't figure how to enter it)

When these dates (10days and 5 days out) trigger, need the emails to include the employee's first/last name and Item Expiring(like CDL License) with the date.

Many many thanks for any help. I hope I have followed the guidelines and explained it properly.

Code:
Private Declare  Function ShellExecute Lib "shell32.dll" _ 
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _ 
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _ 
ByVal nShowCmd As Long) As Long 
Sub SendEMail() 
    Dim Email As String, Subj As String 
    Dim Msg As String, URL As String 
    Dim r As Integer, x As Double 
    For r = 2 To 3 'data in rows 2-3
         '       Get the email address
        Email = Cells(r, 8) 
         
         '       Message subject
        Subj = "Upcoming Expiration Date(s)" 
         
         '       Compose the message
        Msg = "" 
         'Supervisor Name below
        Msg = Msg & "Dear " & Cells(r, 7) & "," & vbCrLf & vbCrLf 
        Msg = Msg & "The following employee has a due date set to expire on " 
         'Expiration Date
        Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf 
        Msg = Msg & "Troy Farmer" & vbCrLf 
        Msg = Msg & "HR-Safety Director" 
         
         '       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20") 
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20") 
         
         '       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") 
         '       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg 
         
         '       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus 
         
         '       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02")) 
        Application.SendKeys "%s" 
    Next r 
End Sub
trying to figure out how to attach the excel spreadsheet if that is possible

Hi all,

I'm looking for a solution to a tricky problem I'm having here...

I have 4 columns with dates in them, the first based off of a start date, and each other based off the last (ie B1 is based on A1, C1 is based on B1, etc)

Each date is linked to an action that somebody needs to take. If they take the action, they input Y into a column (cleverly titled "done"). If they don't, they leave it to the default "n". I have an advanced filter set up to only show me things to be done today.

What I need is to be able to change the date based on whether or not the condition is met. So, I have 10 things I need to do today. I check "y" on 8 of them. 2 of them aren't checked, and I need them to pop up tomorrow.

If I could assume that if something was put off until the next day it'd get done then and not get pushed back again, it'd be simple, as I'd just use a formula like:

IF(IF(LEN(J41)>2,WORKDAY(B$9,0,M$9:M$32),0)"

I have a contact list with thirteen columns, and I've just added a fourteenth column for "Last updated on:"

I would like to create a little script such that whenever anything is added/modified in the first thirteen columns, it'll hop over to the fourteenth column and automatically put the date in the "Last updated on:" column

I know I need to do something like the following:

------

Sub Worksheet_Change(ByVal Target As Range)

***Magic code here***
ActiveCell.Value = Date

End Sub

-------

How do I get it to slap this information in the rightmost, fourteenth, column?

Thanks!

I would like to enter a date (not today's date) on Worksheet 1 and then
update it automatically on Worksheet 2.

Worksheet 1:
January 1, 2009

Worksheet 2:
January 2, 2009

What formula would accomplish this?

Update all formulas on spreadsheet - including array formulas by using
something similar to a find & replace function. Example change Apr05! sheet
reference in all formulas to May05!.

Hello,

I have been trying to get this formula to work, and it appears it only works when it wants to and I cannot figure out why. Can anyone assist? Perhaps a whole new formula suggestion?

What I'm trying to do: I have a spreadsheet with several columns of data, but I want the formula to read only two entire columns for a specific criteria for each column, then display the most recent date based off the criteria. I do not need to total up the criteria, just display the last date.

This is what I have so far:
=IF((AND(Sheet2!A:A=B2,Sheet2!B:B="Game: Counter Strike - Source")),MAX(Sheet2!C:C))

I have this in a table, header as "last date" which is (C2), the B2 in my table would be where criteria would be typed in.

So basically, my table should pull the last date from based off the criteria from raw data in sheet2, that is entered in to B2 on sheet1 and is "Game: Counter Strike - Source".

Thanks for the help in advance.

Can someone please refresh me on the code to locate the
last occupied cell on a spread sheet, even if data on
spreadsheet is non-contiguous. Specifically Row number &
Column number of the last cell used. I am not referring
to .Find("*")

Thank you.

Ok...here's a full explanation of what i'm trying to do.....

column b contains the date for each entry. i would like for cell e6 to update +60 whenever the MOST recent entry (date found in b column, value found in k column) is >0 is put into any cells from k10:k365. all i know is that i'll need something like a max(b10:b365). in case that didn't all come out clear.......

i put a date in column b
if i put a value >0 in k column....
....i want the e6 cell to show +60 days from whatever the most RECENT date is with the most RECENT k column data, so it's always updating another 60 days into the future whenever i put a value into the k column, based off whatever the last date had a value in the k column....have i thoroughly spoken in circles? thanks ahead of time.

I dont know if this can be done, i have had a request from someone who has asked can excel send a email to outlook once a certain date has been reached on his excel spread sheet.

Example:

Spreadsheet date 21/03/08
Todays date 21/03/08

Email sent to outlook stating something like, "You have a case due today"

Very much doubt this can be done.

Thanks in advance

DJ

Hi there,

I'm desperate!

Basically, I have a spreadsheet calculating lab supplies. The master spreadsheet calculates what has been used and warns when things need to be reordered. The lab manager would also like the date an item was last ordered on this sheet.

A second tab holds all the information on when items have been used, reordered and stocktakes, with the date.

I have three columns of data. I need, for example, the latest date gloves were reordered ... visually, that would be 28/9/10. So I need a formula that finds the last date where column C has a value.

A B C
date item reorder qty
25/09/2010 gloves 2
26/09/2010 masks 2
27/09/2010 gloves
28/09/2010 gloves 2
29/09/2010 gloves
30/09/2010 masks 4
01/10/2010 ruler 3
02/10/2010 gloves
03/10/2010 masks
04/10/2010 ruler 1

Thanks!

What is the best way to display the time and date of the last link updated to an external workbook?

Thanks Nunzio

In this spreadsheet (attatched) I wish to create totals in columns D and E which are linked to the last date of each month in Column A. Is there a formula I can create so that it will automatically add together totals on the last day of each month.?
And if so: the dates running down Column A miss out sundays, what happens if the month ends on a sunday?

Hi,
I need to find the last used cell in a particular sheet to further calculations. i use following code

	VB:
	
 
 
 
Const BottomRowNum = 65536 
 
 
Function GetLastUsedCell(ColNum As Long) As Long 
    Dim LastUsedCell As Long 
    Dim UsedCellCount As Long 
    Dim LowerCellRange As Range 
    Dim CurrentCell As Range 
     
     ' Ensure function recalculates each time worksheet is updated
     
    Application.Volatile 
    With ActiveSheet 
         
         ' Check that bottom cell is not empty
         
        If Not IsEmpty(ActiveSheet.Cells(BottomRowNum, ColNum)) Then 
            GetLastUsedCell = BottomRowNum 
            Exit Function 
        End If 
         
         ' Estimate position of last used cell
         
        LastUsedCell = .Cells(BottomRowNum, ColNum).End(xlUp).Row 
        If LastUsedCell = 1 Then 
             
             ' Check cell as it may be empty.  If so, return 0.
             
            If IsEmpty(.Cells(1, ColNum)) Then 
                GetLastUsedCell = 0 
                Exit Function 
            End If 
        End If 
        Set LowerCellRange = Intersect(.UsedRange, .Range(.Cells(LastUsedCell + 1, _ 
        ColNum), .Cells(BottomRowNum, ColNum))) 
        If Not LowerCellRange Is Nothing Then 
             
             ' Check for hidden non-empty cells
             
            UsedCellCount = Application.WorksheetFunction.CountA(LowerCellRange) 
            If UsedCellCount > 0 Then 
                Set CurrentCell = .Cells(LastUsedCell + LowerCellRange.Rows.Count, ColNum) 
                 
                 ' Check vertically from bottom (of LowerCellRange) until first hidden non-empty cell is found
                 
                While IsEmpty(CurrentCell) 
                    Set CurrentCell = CurrentCell.Offset(-1, 0) 
                Wend 
                LastUsedCell = CurrentCell.Row 
                Set CurrentCell = Nothing 
            End If 
            Set LowerCellRange = Nothing 
        End If 
    End With 
    GetLastUsedCell = LastUsedCell 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I use the function on sheet1 and it works fine, but when I go the sheet2 and calculated it return the last used cell on sheet2 instead of sheet1 where the function is entered and should be used.
I am certain it is a easy fix, but as I am new to VBA I have trouble soving the problem.
Thanks


No luck finding an answer? You could always try Google.