Results 1 to 5 of 5

Thread: Update data from closed workbook with sumif formula

  1. #1
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0

    Update data from closed workbook with sumif formula

    Hi Dear all,

    I was trying to link two workbooks for manpower utility...
    The workbooks are as below.

    1.) Daily Workbook include the details of work (in column G) and Time Taken (in column F). This workbook is filled by the individual.
    2.) A Master Workbook include the team member list (in C4 to AD4) , Month list (in B6 to B17).

    The attached files are for the ref. XYZ file is filled by individuals and output file is the Master workbook

    I was trying to update the link bychanging the work (Parameter to calculate the hrs used) from B3. Please suggest me what kind of formula / VB code to be applied in to the Master Workbook????


    Please Help....


    Thanks in advance..

    Leo
    Attached Files Attached Files
    Last edited by leopaulc; 11-01-2011 at 04:23 PM.

  2. #2
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0
    Hi.....

    Thanks for the response.....

    The workbook will not be open all the time.. The XYZ Kind of files will be approx 25. The all files are not possible to open to update the output file.. is there any other option to link the inactive file? (Similar to Update links)??????


    Thanks in advance

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Leo,

    In my opinion, you must run a consolidation macro to update your Master workbook. You could find some useful links here
    If you need further help on this or macro, post back.. happy to help.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    You could use the entire path of the file within the formula

    Like for example

    =SUMIF('G:\ExcelFox\[Name of the workbook.xlsx]NameOfSheet'!C6:C14,"FOX",'G:\ExcelFox\[Name of the workbook.xlsx]NameOfSheet'!B6:B14)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    I accidently delete my earlier post.

    It can be done using INDIRECT function. However, to work with INDIRECT the other workbook must be open.

    PFA.
    Attached Files Attached Files
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 2
    Last Post: 05-30-2013, 07:28 PM
  2. Replies: 5
    Last Post: 04-07-2013, 05:11 PM
  3. Compiling Workbook Data..
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 4
    Last Post: 01-23-2013, 01:21 PM
  4. Merging Data from many workbooks in to one workbook
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 14
    Last Post: 10-10-2011, 11:26 AM
  5. Split Closed Workbook into Multiple Workbooks Using ADO
    By ramakrishnan in forum Excel Help
    Replies: 4
    Last Post: 10-02-2011, 08:34 PM

Posting Permissions

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