Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

external reference designated by cell value

Copper 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.

4 Replies

@TimEnt006 You need INDIRECT() function.

 

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

 

 

best response confirmed by Hans Vogelaar (MVP)
Solution
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? 

@TimEnt006 I have the same issue. I am having better solutions with INDEX, but still not live. I need to have manually written the whole address to the workbook tab, and then I can by formula select a cell. But it would be more workable if I could also make as formula the filename, address, and workbook too.

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution
wow, thanks, that works. thank you so much (I spent all day yesterday trying to work this out for myself) thank you

View solution in original post