+ Reply to Thread
Results 1 to 13 of 13

Looking to compile data from daily sheets onto 1 master sheet

  1. #1
    Registered User
    Join Date
    10-12-2012
    Location
    Iowa
    MS-Off Ver
    Excel 2003
    Posts
    8

    Looking to compile data from daily sheets onto 1 master sheet

    Hey all, currently I have been working with a production log here at work for awhile. Im an inventory control Manager for a flour processing mill, so I handle imputting dailiy production totals for the mill every morning. Plant Manager had asked me for the totals of product "X" made in 2011, there was no easy way to do that aside from going through every daily mill report and add them all up.

    So what I am looking to do is when I imput "value 1" into a cell, it adds that value to a seperate excel file that I can keep seperate and backed up. Preferably I would like that excel file to include each month on their own sheets with the days included. There are a total of 16 items that I have to maintain inventory on. Here is a forumula I tried using at first "='C:\Users\Corey.Gesell\Desktop\Daily Mill Reports\[DR 10-14-12.xlsx]Sheet1'!$D$28", but I would have to do that for every single cell and it just didnt make sense to do it that way.

    Was hoping someone could point me in the right direction or offer some advice, I am pretty familiar with Excel, just havent really gotten into VBA and macros.

    Thanks in advance for any help offered!

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Looking to compile data from daily sheets onto 1 master sheet

    Can you post a sample?
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Looking to compile data from daily sheets onto 1 master sheet

    You can have a macro that consolidates the data for you from each sheet or file.

    Then you can have a pivot created on that consolidated data which gives you the figures you need.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Registered User
    Join Date
    10-12-2012
    Location
    Iowa
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looking to compile data from daily sheets onto 1 master sheet

    @xladept - Having issues trying to upload a screenshot of one of my logs, I will keep working at it.

    @arlu1201 - Have checked into some macros, but havent had any luck. Can you give me a little more info on what you had in mind? Thanks!

  5. #5
    Registered User
    Join Date
    10-12-2012
    Location
    Iowa
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looking to compile data from daily sheets onto 1 master sheet

    @arlu1201 - Tried to do a pivot table. I am needing data from 2 seperate rows, but I keep getting an error stating the date source referrance is not valid

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Looking to compile data from daily sheets onto 1 master sheet

    What do you mean by you need data from 2 separate rows?

    Do not upload a screenshot, instead just create a dummy file which has the same layout as your original file. Then upload that file using this method -
    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.

    Looking at your file, i can help you create a macro that will consolidate the files into 1 file. You just need to tell me which fields need to be consolidated.

  7. #7
    Registered User
    Join Date
    10-12-2012
    Location
    Iowa
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looking to compile data from daily sheets onto 1 master sheet

    @arlu1201 - Ill get that upload on Monday or Tuesday, I do not have excel at the file at home with me, what I will do is
    upload a blank log and let you know which cells I am looking to consolidate onto that new file, thanks again!

  8. #8
    Registered User
    Join Date
    10-12-2012
    Location
    Iowa
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looking to compile data from daily sheets onto 1 master sheet

    @arlu1201 - Here is the blank form. I do one of these forms every day to add/subtract from inventory. What I am hoping to do is to have a seperate file that adds in data entered from cells B29-N29 and B35-N35. On that seperate file it would look like the "Production Totals 2012" file attached. The cells would copy over to the matching columns by date, which I was not sure would be possible or not... Hopefully this helps, if you have any questions or think there would be a more logical way to do this please this let me know!
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-12-2012
    Location
    Iowa
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looking to compile data from daily sheets onto 1 master sheet

    Back in the office today and looking to work on this, hoping for some help!

  10. #10
    Registered User
    Join Date
    10-12-2012
    Location
    Iowa
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looking to compile data from daily sheets onto 1 master sheet

    Is there anyone here able to help out? Busted my brain playing around with different ideas today when I had some downtime and still couldn't get anymore. Really would appreciate it!

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Looking to compile data from daily sheets onto 1 master sheet

    So the macro should run from the Production Totals report?

    Do you want that it asks the user to open up the corresponding Blank Mills Report for the day and then copy the values?

  12. #12
    Registered User
    Join Date
    10-12-2012
    Location
    Iowa
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looking to compile data from daily sheets onto 1 master sheet

    In a perfect/simple world what I would like it do to is when I open up the blank daily mill report and start adding in the totals for the day into rows 29 and 35, it would add those amounts under the corresponding columns on the Production Totals form. Once I finished filling in the amounts on the blank daily mill report I then save it as the day of production. Definately seems easier said then done, and if there is a simpler way to do it then I am all ears. If all else ways I can simply just copy and paste the amounts over lol.

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Looking to compile data from daily sheets onto 1 master sheet

    Hi coreygesell,

    Try this - you may need to adjust the book and sheet names:

    Please Login or Register  to view this content.

+ 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