external reference designated by cell value

New Contributor

I'm wanting to link to cells to a different workbook where the workbook name and worksheet name are designated by cell values.

 

i.e. =A1 in workbook "Tango" and work sheet "Alfa"

So this works  =[Tango.xlsx]Alfa!A1 fine,

but I'm wanting to reference the workbook Tango (in cell B3) and the workbook Alfa (in cell B5)

I was thinking it would look something like this =[B3]B5!A1 but I can't get it to work.

any suggestions?

Obviously once I get the principle working my formulas will get more complicated but I need to get this referencing right first.

3 Replies

@TimEnt006 You need INDIRECT() function.

 

=INDIRECT("["&B3&"]"&B5&"!A1")

 

 

wow, thanks, that works. thank you so much (I spent all day yesterday trying to work this out for myself) thank you

@Harun24HR as I am now using the INDIRECT function this only works when the source workbook is open. Is there anyway to get this working without having the source open?