Excel INDIRECT Function Range not Valid

0
votes

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

1
excelexcel-formulaexcel-indirect
Your second apostrophe should be before the exclamation mark: 'C:\Users\me\Documents\C251\[C251output_powereditor.xlsx]C251'!G4:G38 and there is no point including a path given that it won't work unless the workbook is open. ;) – Rory

1 Answers

1
votes

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)