Results 1 to 3 of 3

Thread: How to automatically pull data from master list to state required form

  1. #1
    Neophyte Ivory's Avatar
    Join Date
    Jul 2019
    Location
    Williston, ND
    Posts
    1
    Articles
    0
    Excel Version
    2016

    How to automatically pull data from master list to state required form



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have been quite literally STUCK on this issue for the last month. Hopefully someone will be able to point me in the right direction:

    PROBLEM: Everyday, my site takes in trucks hauling salt water. Each load comes from one specific oil well location and is never in the same order on any given day nor the same amount of barrels hauled. At the end of each month, the state requires us to complete a form that includes how many barrels we in took for that month, by each individual well pad. As seen in (Sample_MasterList), each one of these wells have legals (file number, location) determined by the state that also must be included in this form. Due to the horribly formatted form provided and required to use per the states requirements (FORM16TEMP), I am not able to just simply copy and paste neither rows or columns of data together; the form has merged multiple cells together and it is also password protected.

    WHAT I HAVE DONE SO FAR: Prior to me signing on, this process took DAYS to complete. I've revamped the excel spreadsheet (Sample_DailyLogs) used to input the daily logs so that now it includes a pivot table at the end of the month's workbook that automatically tallies these totals. I also put together a "master list" of well locations with their corresponding legals (Sample_MasterList). This list included roughly 8000 wells and their legals. By running a macro, I was able to retrieve the password for the state's form yet trying to reformat it in a way which allows simple copying and paste, lets just say has turned into a nightmare.

    WHAT I NEED HELP FIGURING OUT: Is there any way I could type in the well's name on the form and have its corresponding legals auto populate from the master list I have created? Or even better, have the form populate itself from the pivot table in the daily log workbook we use to calculate the monthly total's individually? I'm open for ideas on how to make this work where I am not having to copy and paste 5 times in order to input one row of data in a list of 80-100 rows of data.
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,568
    Articles
    0
    Excel Version
    365
    This should be perfectly possible and will save you oodles of time and be more accurate.
    We should be able to fill in ALL the fields in FORM16 automatically.

    Excel says the SampleDailyLog file you attached needs repairing when I open it - there seem to be phantom sheets in it and the repairing process loses all pivot tables. There's also a connection to a file called Add to End table Monthly-Daily Log Sum Template1.xlsm which of course I don't have, that may be fouling things up - I don't know. So I don't know how you've created the "pivot table at the end of the month's workbook" where it gets it data from and what it shows.

    The daily sheets are filled out by others and sent to you as an Excel file?
    There is no date info on each sheet, only the sheet's name which seems to be a day of the month? It would be better if there were also month and year information at least in one place on those sheets.
    Why? Because I would consolidate these daily sheets onto a single sheet, perhaps within your MasterList, or a completely different file. Each record (row) would have full date and time info, the legals at that time (at the time the log was consolidated), and everything else available from the DailyLogs. Code could do this for you, including tidying up the data (some times seem to be a little odd).

    Then when it comes to filling in a form, you'd supply the well's name, perhaps the month and year too, and the rest would be done for you - but it does mean consolidating those daily logs into a database-like format which you'll be able to query in a million ways later too for other purposes and reports, including historic data. It would be easy to fill out multiple forms for multiple wells at once.

    I'll put together some example code to do some consolidation with the data I've got, but it won't produce brilliant results since I since I don't have all the wells' legals in the MasterList, nor do I have month/year data.

    As and when I get time (and the inclination)…

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,568
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    As and when I get time (and the inclination)…
    ...and a signal from you that you haven't lost interest.

Posting Permissions

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