0

I'm trying to automate a tedious process that involves opening a file that consistently gets renamed and copying and pasting cells from it into another workbook.

I am able to use the concatenate and indirect functions to get a dynamic filename & sheet but I can't reference this filename in an index function.

The function with manually input filenames/sheet/cell reference works fine though so I know it's just a matter of getting excel to recognize my dynamic filename reference in the index function.

A1 = 1
A2 = CONCATENATE("'[Book",A1,".xlsx]sheet1'!A:A") 
A3 = INDIRECT(A2)                              
    = CELL("address",INDEX(A3,MATCH(B1,A3,true)))    'where B1 is my reference and does exist in A:A
    = CELL("address",INDEX(A2,MATCH(B1,A2,true))) 

=CELL("address",INDEX([Book1.xlsx]sheet1!A:A,MATCH(B1,[Book1.xlsx]sheet1!A:A,TRUE)))             ' this is my desired function which works fine for me

I get a #N/A reference when I try both the indirect/concatenate functions. As I noted above, my function works fine if I manually input the dynamic value but not with the indirect value or the concatenate value.

Any help would be greatly appreciated

otnaisenad
  • 1
  • `INDIRECT`is a volatile function, that is why you are getting this error. Check https://stackoverflow.com/questions/757413/excel-reference-to-current-cell/22264393#22264393 – David García Bodego Oct 13 '19 at 03:08
  • A simple piece of VBA can solve this too, without opening that file. – JvdV Oct 13 '19 at 06:49

0 Answers0