+ Reply to Thread
Results 1 to 7 of 7

Trying to create an auto-update rule

  1. #1
    Registered User
    Join Date
    01-09-2019
    Location
    canada
    MS-Off Ver
    2016
    Posts
    3

    Trying to create an auto-update rule

    Hi folks,
    I am trying to create a rule for auto fill for the log sheet in my lab. As we can see in the attached image, the serial number (red color) in column A, have to come in column G, in between some other remarks.
    For a new day, what I am doing currently is, I copy the entire lines, update the column of serial number by increment by 1. Once i get the new set of serial numbers, I manually replace each older serial number in column G with the new one. If there is an option to setup an auto-update, it could have been so easier.
    Thanks.
    book1.PNG
    Attached Files Attached Files
    Last edited by elbyraju; 01-11-2019 at 02:28 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    46,558

    Re: Trying to create an auto-update rule

    Hi, welcome to the forum

    For the increment, try his...
    =--LEFT(A2,LEN(A2)-1)+1&"A"
    (this should cater for when you go past 9999)
    Although, I notice you are not using A in col G, but other letters?

    For the 2nd part, where are those extra parts coming from?
    If they are from another cell, you could use something like...
    =D2&A2&C2
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-09-2019
    Location
    canada
    MS-Off Ver
    2016
    Posts
    3

    Re: Trying to create an auto-update rule

    Getting increment is not a problem, i usually drop down the serial number and right click-on bottom right of the cell and select fill series.

    Each of the serial numbers represent a part. same part will be checked in 3 shifts (A,B and C respectively).

    Time in and out for A shift will have separate columns whereas, time for B and C is recorded in remarks column.

    And the other number in the remark column (3323,3421, 3521) refers to which place the parts belong to.

    so these 6 lines of data, are like a template where most of it stays the same except for the serial number (column A and G)

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    13,607

    Re: Trying to create an auto-update rule

    VBA, if you like.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-09-2019
    Location
    canada
    MS-Off Ver
    2016
    Posts
    3

    Re: Trying to create an auto-update rule

    thanks for the reply, but unfortunately, i couldn't get it working. could you please explain?

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    13,607

    Re: Trying to create an auto-update rule

    When you click the button, Col.G numbers in red will be replaced with the number in col.A in the same row.

    Isn't is what you wanted?

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    46,558

    Re: Trying to create an auto-update rule

    Getting increment is not a problem, i usually drop down the serial number and right click-on bottom right of the cell and select fill series.
    Not sure how that would happen, but if it works for you, thats great

    Each of the serial numbers represent a part. same part will be checked in 3 shifts (A,B and C respectively).
    I dont see anything there to reference a shift - unless that is what is in col A?

    Time in and out for A shift will have separate columns whereas, time for B and C is recorded in remarks column.
    Still dont see where A comes into this, but OK

    And the other number in the remark column (3323,3421, 3521) refers to which place the parts belong to.
    So, where will that data come from? If you want to automate this, then why not automate as much as possible?

    so these 6 lines of data, are like a template where most of it stays the same except for the serial number (column A and G)
    [/QUOTE]
    If the "changeable" data is shown somewhere, the rest can be calc'd

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1