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))
Bookmarks