In a column, I´m storing ranges as plain Text. I then want to use these ranges in a formula. As everything so far was in the same workbook I had no issue. Now I want to get value from another workbook so I added just the path of the file just in front of my range.
It gives me something like that (stored in cell R38):
'C:\Users\me\Documents\C251\[C251output_powereditor.xlsx]C251!'G4:G38
Then I' m trying to use the following formula :
MATCH("Stlnr.";INDIRECT(R38);0)
But I got a ref error.
If I try the following :
MATCH("Stlnr.";[C251output_powereditor.xlsx]C251!G4:G38;0)
It does work.
I´m not sure what the issue is with my indirect function. And before you ask the other workbook is open. :) Thanks in advance
I think your problem could be that when you enter
'C:\Users\me\Documents\C251[C251output_powereditor.xlsx]C251!'G4:G38
into a cell, Excel treats the first ' as the symbol of starting a text field, so it thinks the path is C:\Users\me\Documents\C251[C251output_powereditor.xlsx]C251!'G4:G38.
Solution: Add a single quote either in the formula or in the data cell:
''C:\Users\me\Documents\C251[C251output_powereditor.xlsx]C251!'G4:G38
or
MATCH("Stlnr.";INDIRECT("'"&R38);0)