Concatenate to create a filepath string and using indirect.ext to look up value

Hi

I want to sheet that looks up values from external workbooks several times.

I'd like to be able to have a cell for the filepath of the workbook and another for the filename.

I'd use CONCATENATE to generate a string which creates the filepath and cell reference.

Then pass that string into INDIRECT.EXT to return that value.

Eg.

D4 contains a filepath: P:\Projects\Project1\

E4 contains a filename: Workbook.xlsx

F4 contains the CONCATENATE function (I'd like to reference Cell C30 on Sheet called 'Data'): CONCATENATE("'",D4,"[",E4,"]Data'!$C$30")

INDIRECT.EXT(F4) returns a #REF!

Can anyone help?

thanks

Hi - could it be because it needs to be INDIRECT.EXT(F4) and not F6?

Or perhaps that'a typo in your message. Sorry, can't help more. :-)

Cheers

Hi,

I personally don't have the INDIRECT.EXT Add-In installed on my machine, though, assuming it does not differ in syntax from the standard worksheet function INDIRECT, I can see nothing at all wrong with your formula.

I can only suggest you re-check your cell entries in D4 and E4 and make sure they are correct, e.g. is the entry in D4 the full filepath? Is the extension still .xlsx, and not perhaps been (re-saved) as an .xls?

Regards

Regards