+ Reply to Thread
Results 1 to 2 of 2

Need assistance on nesting an indirect function in a index / match formula

  1. #1
    Registered User
    Join Date
    09-25-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    1

    Need assistance on nesting an indirect function in a index / match formula

    Need assistance on nesting an indirect function within an index function, every attempt I make gives me a #REF! error.

    I would like to retrieve or reference data from:

    Workbook: check_ledger.xls
    Worksheet: Store1

    And place the data in the following workbook/worksheet, by nesting the indirect function into the index formula.

    Workbook: workbook1.xls
    Worksheet: import


    My original index formula referenced the data from the same workbook, however, the data has now moved to a separate workbook.

    I use workbook1.xls to import into our accounting program and would like to keep the data that it references in a separate workbook.

    To make implementation easier on me, I would like to reference the workbook name & worksheet name using the indirect function.

    My original working formula without indirect functions can be found below:

    =INDEX(store1!F$1:F$1204,MATCH(1,(store1!$A$1:$A$1204=$A5)*(store1!$B$1:$B$1204=$B5)*(store1!$C$1:$C$1204=$C5)*(store1!$D$1:$D$1204=$D5),0))

    My new non-working formula with indirect function can be found below:

    A2 = check_ledger
    B2 = store1

    =INDEX(INDIRECT("'["&A2&".xls]"&B2&"'!F$1:F$1500"),MATCH(1,INDIRECT("'["&A2&".xls]"&B2&"'!$A$1:$A$1500="&$A4&"")*INDIRECT("'["&A2&".xls]"&B2&"'!$B$1:$B$1500="&$B4&"")*INDIRECT("'["&A2&".xls]"&B2&"'!$C$1:$C$1500="&$C4&"")*INDIRECT("'["&A2&".xls]"&B2&"'!$D$1:$D$1500="&$D4&""),0))

    Samples of both workbooks can be found attached.

    Any help or guidance is much appreciated.

    **SOLVED**, Solution below:



    I was able to solve my problem.

    I made a change in the match formula. For the look up array, I put the indirect function(look up criteria) second and by doing that, I had separated it from the first look up criteria (E.g. "A4").
    I hope i'm explaining this correctly, perhaps it would be better to visually see the changes:

    Partial Non-Working Formula:
    MATCH(1,INDIRECT("'["&A2&".xls]"&B2&"'!$A$1:$A$1500)="&$A4&"")*

    Partial Working Formula:
    MATCH(1,($A4=INDIRECT("'["&A$2&".xls]"&B$2&"'!$A$1:$A$1500"))*


    I listed both attempts below to get a clearer look on the changes.


    Full Non-Working Formula:
    =INDEX(INDIRECT("'["&A2&".xls]"&B2&"'!F$1:F$1500"),
    MATCH(1,INDIRECT("'["&A2&".xls]"&B2&"'!$A$1:$A$1500="&$A4&"")*
    INDIRECT("'["&A2&".xls]"&B2&"'!$B$1:$B$1500="&$B4&"")*
    INDIRECT("'["&A2&".xls]"&B2&"'!$C$1:$C$1500="&$C4&"")*
    INDIRECT("'["&A2&".xls]"&B2&"'!$D$1:$D$1500="&$D4&""),0))

    Full Working Formula:

    =INDEX(INDIRECT("'["&A$2&".xls]"&B$2&"'!F$1:F$1500"),
    MATCH(1,($A4=INDIRECT("'["&A$2&".xls]"&B$2&"'!$A$1:$A$1500"))*
    ($B4=INDIRECT("'["&A$2&".xls]"&B$2&"'!$B$1:$B$1500"))*
    ($C4=INDIRECT("'["&A$2&".xls]"&B$2&"'!$C$1:$C$1500"))*
    ($D4=INDIRECT("'["&A$2&".xls]"&B$2&"'!$D$1:$D$1500")),0))
    Attached Files Attached Files
    Last edited by ghosters; 10-08-2012 at 08:48 AM.

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

    Re: Need assistance on nesting an indirect function in a index / match formula

    Is this solved?

    If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.
    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]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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