Automatically update NOW function

April 24, 2011 at 18:29:18
Specs: Windows 7
I want to create a spreadsheet that has the =NOW funcrion formatted as "hmmss" which displays a number instead of the current date and time. I want to be able to copy and paste that formula and format to another cell and add one to it. So in one cell I might have the number "212404" and in the other cell I would have the number "212405" and when I open the file, both numbers will automatically update. Thanks for any help

See More: Automatically update NOW function

Report •


#1
April 24, 2011 at 18:50:29
The =NOW() function is referred to as a volatile Function and will recalculate whenever any formula within the entire Workbook recalculates, regardless of cell references and whenever a workbook is opened or saved.

So you could probably get it to display as "hmmss"
and it will update when you open or save a worksheet,
BUT it will also update every time you do a calculation.
Just entering data into a cell can prompt a recalc.

If you explain what it is your trying to accomplish in a bit more detail, we may be able to offer some alternatives.

MIKE

http://www.skeptic.com/


Report •

#2
April 24, 2011 at 18:59:02
I am trying to create a form that has a unique document number in one corner. Each form would be on a separate sheet and each sheet would have a unique document number. I don't want to use the same document number twice and every time I open this spreadsheet, the document number is updated automatically as the NOW function does.

Report •

#3
April 24, 2011 at 19:01:15
I can send you my Excel file so you can visually see what I am trying to do if this would help. If so, where do I send it to? Thanks!

Report •

Related Solutions

#4
April 25, 2011 at 11:57:53
I don't want to use the same document number twice and every time I open this spreadsheet, the document number is updated automatically as the NOW function does.

=NOW() will recalculate whenever any formula within the entire Workbook recalculates
When you first open the spread sheet it will be 1
When you enter any data in any cell it will become 2
When the sheet recalculates it will become 3
ETC, ETC,

I don't think that is what you want. Is It?

I believe what your looking for is a Unique ID number that will be inserted, the First time the record is created and thereafter remain static.

If that is what you need then your best bet is a Macro.

MIKE

http://www.skeptic.com/


Report •

#5
April 26, 2011 at 05:15:47
Mike,
Can you tell me how to create a macro or VB code that I can attach to a button that increases a number in a specific cell by one every time I click the button? Thanks

Report •

#6
April 26, 2011 at 10:58:23
To add one to the Selected cell:

Sub AddOneToSelection()
  Selection = Selection + 1
End Sub

To add one to a specific cell, e.g. D45:

Sub AddOneToRange()
  Range("D45") = Range("D45") + 1
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
April 27, 2011 at 08:28:58
Thanks so much for the VB cvode. It works great and I appreciate it.

Report •


Ask Question