-

Jump to content



Photo
- - - - -

Another Excel Question


This topic has been archived. This means that you cannot reply to this topic.
5 replies to this topic

#1 of 6 drobbins

drobbins

    Screenwriter

  • 1,870 posts
  • Join Date: Dec 02 2004

Posted October 12 2007 - 04:42 AM

Is there a formula in excel that I can use to automatically add the current date, but once it is there, it will not change each day? Example: If I enter data in A1, then B1 will have today’s date entered automatically. Then tomorrow I enter info in A2 and B2 has tomorrows date entered automatically. The date in B1 did not change from today’s date.
Thanks,

#2 of 6 Tekara

Tekara

    Supporting Actor

  • 783 posts
  • Join Date: Jan 08 2003

Posted October 12 2007 - 12:50 PM

Sure, you'll need to use some visual basic though. This page will help with events and it covers using worksheet|change which is what you'll need to check for A1 changing, then you'll just have B1 change it's contents to today(). Make sure to lock down B1 so it can't be manually edited by mistake later!

http://www.cpearson....cel/Events.aspx

Hope that helps
"Computers are a lot like air conditioners - they both work great until you open windows." -Anonymous
"The danger from computers is not that they will eventually get as smart as men, but that we will agree to meet them halfway." -Bernard Avishai

#3 of 6 drobbins

drobbins

    Screenwriter

  • 1,870 posts
  • Join Date: Dec 02 2004

Posted October 13 2007 - 01:39 AM

Thanks for the reply. It looks a little more complicated than I expected, but I will give it a try.

#4 of 6 SethH

SethH

    Screenwriter

  • 2,867 posts
  • Join Date: Dec 17 2003

Posted October 13 2007 - 08:50 AM

I would take a slightly different approach than the one Robert suggests.

It sounds like you will be updating this daily. For instance, today you will put data in A1, tomorrow in A2, etc. That would require some excessively complicated VBA, IMO (unless I'm overlooking something).

I would instead create a button and call it "Date Stamp" or something like that and require the user to click this button after entering that day's data in column A. Put something like this in the button_click event:

Sub Button_Click()
Dim rngDateCell as Range

Set rngDateCell = Range("B65536").End(xlup).offset(1,0)
rngDateCell = format(now(),"Long Date")

End Sub

You should also have some error handling and such in there, but I think that will do what you want assuming you will always use consecutive rows to input the data.

#5 of 6 drobbins

drobbins

    Screenwriter

  • 1,870 posts
  • Join Date: Dec 02 2004

Posted October 15 2007 - 03:48 AM

Seth,
Yes, Data is going to be entered daily on this sheet. This Button, is it located on the tool bar or in the spreadsheet? This spreadsheet is located on a company shared drive and is accessed by multiple users.

#6 of 6 Tekara

Tekara

    Supporting Actor

  • 783 posts
  • Join Date: Jan 08 2003

Posted October 15 2007 - 10:01 AM

Nah, the the event I suggested can be applied to a range of cells. In this case, your range would just be the singular cell. And it'll update the date cell whenever it's altered, so there's no chance for a mistake in forgetting to press a button.

Although, just like the button you'll need to work up a copy of the code for each cell.
"Computers are a lot like air conditioners - they both work great until you open windows." -Anonymous
"The danger from computers is not that they will eventually get as smart as men, but that we will agree to meet them halfway." -Bernard Avishai