+ Reply to Thread
Results 1 to 2 of 2

Dynamic workbook reference, using Indirect on difference sheet, refering another file~

  1. #1
    Registered User
    Join Date
    08-18-2017
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    12

    Dynamic workbook reference, using Indirect on difference sheet, refering another file~

    Hi All,
    Please refer to the attachment.
    I have a excel file called summary.xlsx, with 2 sheets.
    One sheet is called summary, another is called Helper.
    In the helper sheet, I will have a table with some other excel files, column which I am referring to.
    In the summary sheet I will refer to the helper sheet to gather information from all other files~

    How should I write the formula in summary!summary B column in order to get the information?
    For the sumif condition it wont be repeated~ and I will just type "Steven" in between.
    So I just want to know how can I Write the formula for the first part and the last part in the sumif formula

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,991

    Re: Dynamic workbook reference, using Indirect on difference sheet, refering another file~

    Create named ranges : File_Name, Sheet_Name and Column_Name in "Helper"



    in "Summary"

    in B2

    =SUMPRODUCT(SUMIFS(INDIRECT("'[" & File_Name &"]" & Sheet_Name & "'!" & Column_Name &":" & Column_Name),INDIRECT("'[" & File_Name &"]" & Sheet_Name & "'!A:A"),$A2))

    Copy down


    NotE: ALL workbooks must be open.
    Attached Files Attached Files

+ 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. Using INDIRECT formula to reference a dynamic range on another sheet
    By Travisty in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-01-2018, 12:22 PM
  2. Replies: 5
    Last Post: 11-29-2017, 11:05 PM
  3. [SOLVED] INDIRECT SUBSTITUTE refering to a dynamic list
    By Frankie_The_Flyer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-28-2017, 07:24 PM
  4. [SOLVED] Use INDIRECT to build a dynamic reference is causing #REF error
    By CWatsonJr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-27-2017, 02:37 PM
  5. Replies: 7
    Last Post: 02-19-2017, 02:43 PM
  6. VBA Dynamic Range - An Indirect Cell Reference
    By ExcelHelp2013 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2013, 11:08 AM
  7. [SOLVED] Dynamic VLOOKUP wit INDIRECT reference
    By supern0va in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-29-2012, 11:44 AM

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