+ Reply to Thread
Results 1 to 5 of 5

Help with creating/finding a record keeping-tacker for daily documents.

  1. #1
    Registered User
    Join Date
    06-04-2018
    Location
    Port saint lucie
    MS-Off Ver
    2003
    Posts
    3

    Help with creating/finding a record keeping-tacker for daily documents.

    I work for a metal manufacturing company, and every day I get sheets called burntable sheets from a machine we use to cut metal. With the sheets it tells me what type of metal the thickness the program number in case we need to burn it or use it for reference, What project and structures the burned things foe along with heat numbers (serial numbers for the metal used certifications).
    I have a sheet i enter in all my information and hyperlink the scanned in sheets, The problem is its taking me days to enter, scan, save, hyperlink as well as typing in the info into each correlating cell. Im looking for some help to make this data entry run a little smoother and faster so Im not falling behind on other work. I will attatch the one I use (i made). Would like to see if anyone can help make it look feel and actually be faster so i can stop getting in trouble.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Help with creating/finding a record keeping-tacker for daily documents.

    Deleted Post
    Last edited by mehmetcik; 10-26-2018 at 06:25 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Help with creating/finding a record keeping-tacker for daily documents.

    According to your profile, you are using Excel 2003. Is this true? I see that you are using tables, so you are at least on 2007 - what version do your really have so we know what tools we have available.

    Also, walk us through a sample data entry. How do you get the data? Where to you put it? How does the data on one sheet relate to the other sheets? The more you can tell us how you do this process, the more we can help you.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  4. #4
    Registered User
    Join Date
    06-04-2018
    Location
    Port saint lucie
    MS-Off Ver
    2003
    Posts
    3

    Re: Help with creating/finding a record keeping-tacker for daily documents.

    Thanks for the reply!
    Im using excel-2010 sorry,
    so Here is a walk through. I get them from my production manager, I document whats on the sheet to the cells coordinating so that way its easier to find, so for example, When i get a stack this is what i do.
    DATE CUT JOB STURUCTURE # PROGRAM TASK NAME THICKNESS PARTS QTY HEAT# LINK
    11-11-18 1650 ohs-1 8733 t8135 .500 cpa cpc cpv 15 a7v364 (Hyperlinked .pdf file of this entry)
    once they are all in there I scann them all in and once they are scanned i rename them to this (8733 11-11-18 1650 .500 a7v364.pdf) pdf is added onced saved.
    I then transfer them to the folders i have named. So since this material was cut on 11/11 it would go in the file labeled allburntablesheet\2018\11) November.
    I then go back to my spreadsheet and start hyperlinking.
    Like I said im just looking for a way to make my working process a little faster and smother so in turn can stop getting in trouble for falling behind on these.
    The reason these are important is because the heat # on these sheets are actual serial numbers that correlate to our certifications that are given to us saying it has been tested and certified to use per FDOT standards. So when I get these not only do i have to document them in this excel workbook, But also have to put the heat number in the jobs Material sheet aka Bill of Materials sheet thats in another workbook so we have a list of the parts and heat cert that goes with the part. and once i get to that part I hyperlink the heat number cert in our system to the correlating job so for my example above it would be the 1650 excel sheet and the heat number for the parts mentioned cpa,cpc,cpv is a7v364. I can provide an example mtr sheet if you need it. As you can see its just a lot of back and fourth work but i feel like if i could figure out entering them faster into my little document manager I made I could somehow connect it to the other job workbooks and in time learn to automate it if even possible to where once its entered into the first sheet the rest will auto populate.
    Anything helps. And thank you again. Ive tried to google how to's, Templates for document managers, Records manager, file locators, and nothing has helped.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,920

    Re: Help with creating/finding a record keeping-tacker for daily documents.

    I think I get it but let me re-state it to make sure.

    Scanning is a manual process. After you scan, you rename the document and move it to the appropriate directory.

    Then you go to the spreadsheet and you enter data into the appropriate columns.

    So far, this is all a manual process.

    What I think I can automate for you is linking to the documents based on the sheet name and the information you filled into the columns. However, this will require a very strict naming convention. I suggest that they are all in some common "root" directory. I will let you define this root directory on a Control Panel sheet.

    For Burntable Documents:
    Directory Name = root\All Burntable Sheets\(year)\(month #) (Month Name\
    File Name = (Program) (m-d-yy) (Job) (HEAT #).pdf

    For Angle Cut Documents
    Directory Name = root\All Angle Cut Shtted\(year)\(month #) (Month Name)\
    File Name = (m-d-yy) (Job) (Structure #) ANGLE CUT.pdf

    For Saw Cut Documents
    Directory Name = root\All Angle Cut Shtted\(year)\(month #) (Month Name)\
    File Name = (m-d-yy) (Job) (Structure #) SAW CUT.pdf

    Based on the date and the other information filled in, I can "compute" the expected directory and file names. Then you can highlight those for which you want to make a link and the program will loop through them. If it finds them, it will make the link and shade the cell green. If it can't find the file then it will shade the cell some other color indicating that a link was not made. You can go back and recheck the directory and file name.

    I notice that all sheets have HEAT #. Is this filled in manually? What is the expectation for this number? I am assuming it is first filled in on the Burntable sheet and you want some sort of cross-referencing from the other sheets.

    You are using tables. Tables will replicate formulas going down, but only if each row above it has the same formula. Some of your documents do not match the naming convention described above. You will either have to replicate the formulas manually, or change the legacy data to match the naming conventions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Creating a summary table from daily attendance record
    By saifsawafi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-21-2017, 03:13 AM
  2. Sports Record Keeping Project: Turning weekly scores into full record sheets
    By oneidasfinest1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-31-2015, 09:26 PM
  3. Replies: 3
    Last Post: 06-04-2013, 02:56 AM
  4. Replies: 1
    Last Post: 01-17-2012, 10:47 AM
  5. Keeping Record of Changes
    By greasybob in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-04-2007, 04:30 PM
  6. [SOLVED] Record Excel 2003 Macro to link two documents
    By Dajana in forum Excel General
    Replies: 1
    Last Post: 08-22-2005, 10:05 PM
  7. [SOLVED] Finding last record in month for each of several types of record.
    By Richard Buttrey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-03-2005, 10:06 PM

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