Need help with Microsoft Excel function

Discussion in 'other software & services' started by Noob, Sep 23, 2017.

  1. Noob

    Noob Registered Member

    Joined:
    Nov 6, 2009
    Posts:
    6,491
    So im trying to create a simple spreadsheet to keep track of a small inventory of items and i need excel to create a static time stamp.
    These items have a unique number and the current function already looks up the number in a given range of cells and if it finds it, it places a time stamp but the problem is that every time i open excel it updates all the time stamps.

    This is my current function:
    =IF(COUNTIF($N$3:$N$10003,E13),NOW(),"")

    Ive tried some things that i found on Google but i cant seem to make them work.

    Can anyone help me by modifying the current function or creating a new one?

    Thanks in advance
     
  2. Peter2150

    Peter2150 Global Moderator

    Joined:
    Sep 20, 2003
    Posts:
    20,590
    I think the problem may be that NOW statement. What you need to do is a condition that if that number doesn't exist it does what you have but if it does exist it branches else where.
     
  3. Noob

    Noob Registered Member

    Joined:
    Nov 6, 2009
    Posts:
    6,491
    NOW is the only function that works as a time stamp AFAIK.

    I found that you could create custom functions in VBA in Excel but my knowledge in Excel and any kind of programming is almost non existent to understand it.

    Still havent found any way to solve my issue. Help! :'(
     
  4. xxJackxx

    xxJackxx Registered Member

    Joined:
    Oct 23, 2008
    Posts:
    8,616
    Location:
    USA
    I don't know that I can be of too much help but the problem with NOW is that NOW is always NOW. Every time it reads NOW it is the time right now. This is going to require something advanced or possibly even an external data source (MS Access, etc.). You may get some help here, but if you know of any forums where they discuss Microsoft Office at an advanced level you may have more luck. Or who knows, maybe someone will make me look stupid and post an easy answer. I hope so. Good luck. :thumb:
     
  5. starfish_001

    starfish_001 Registered Member

    Joined:
    Jan 31, 2005
    Posts:
    1,046
    I'm assuming when you add each of the record rows the col with the timestamp is =now()

    if so replace the way you do this with a macro, make a button and paste special values the cell with the timestamp or make them value before you exit

    little macro that Copies col c to col d as values

    Sub makestaticcolD()
    Set myCell = ActiveSheet.Range("c1")
    For i = 0 To 18
    myCell.Offset(i, 0).Copy
    myCell.Offset(i, 1).PasteSpecial xlPasteValues
    Next i
    End Sub
     
  6. roger_m

    roger_m Registered Member

    Joined:
    Jan 25, 2009
    Posts:
    8,627
    You can only do it using a macro as far as I can tell.
     
  7. Noob

    Noob Registered Member

    Joined:
    Nov 6, 2009
    Posts:
    6,491
    Ive been trying to do that circular reference workaround shown in the internet but i cant get it to work.
    In order to make the formula even simpler now i-ve set a column of cells to show if the item unique number is found and show "Yes" and trying to time stamp the other cell with the circular reference workaround but i cant get it to work.
     
  8. act8192

    act8192 Registered Member

    Joined:
    Nov 9, 2006
    Posts:
    1,789
  9. Noob

    Noob Registered Member

    Joined:
    Nov 6, 2009
    Posts:
    6,491
    Thanks for the replies guys. After trying all recommendations shown in Google results, i found one that works.

    What i did was set a column exclusively for showing if the number was found in a range of cells, if it was found then it said yes, then in another cell i used a circular reference function as recommended and it worked. Now the dates no longer auto update.
     
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.