SOLVED
Home

To Link Excel Books using cells with text

%3CLINGO-SUB%20id%3D%22lingo-sub-799831%22%20slang%3D%22es-ES%22%3ETo%20Link%20Excel%20Books%20using%20cells%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799831%22%20slang%3D%22es-ES%22%3E%3CP%3EDear%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20link%20the%20values%20of%20several%20excel%20books%20to%20a%20main%20excel%20workbook.%20The%20process%20using%20the%20copy-paste%20function%20with%20all%20the%20excel%20sheets%20opened%20is%20simple%2C%20however%2C%20when%20I%20try%20to%20link%20the%20values%20by%20hand%20(using%20the%20file%20domain)%20it%20doesn't%20work.%20The%20problem%20comes%20from%20a%20failed%20syntax%20when%20I%20replace%20the%20original%20domain%20between%20the%20single%20quotes%20with%20a%20cell%20with%20the%20%22text%22%20of%20that%20domain.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Example%3A%3C%2FP%3E%3CP%3E-Original%3C%2FP%3E%3CP%3E%22C%3A'CVR%22Folder1%22Folder1-1%22Folder1-1-1%22%5BResultsExcel_3Dv11.xlsx%5DCV%20Average%20Temperature'!%24B2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-Fail%20case%3C%2FP%3E%3CP%3E'%5BA1%5D'!%20%24B2%3C%2FP%3E%3CP%3Ebeing%20the%20A1%20value-C%3A-CVR-Folder1-Folder1-1-Folder1-1-1-%5BResultsExcel_3Dv11.xlsx%5DCV%20Average%20Temperature%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20do%20this%20process%20because%20I%20need%20to%20do%20some%20changes%20in%20the%20links%20to%20the%20different%20excel%20workbooks%20such%20the%20ones%20that%20you%20can%20see%20in%20the%20attached%20example%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20your%20answers%20and%20kind%20regards%2C%3C%2FP%3E%3CP%3ECharlie%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-799831%22%20slang%3D%22es-ES%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-801355%22%20slang%3D%22en-US%22%3ERe%3A%20To%20Link%20Excel%20Books%20using%20cells%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-801355%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F390822%22%20target%3D%22_blank%22%3E%40cvazquez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20it'll%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DINDIRECT(%22'%22%26amp%3BA8%26amp%3B%22'!%24B2%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ebut%20the%20file%20you%20linked%20shall%20be%20opened%2C%20INDIRECT%20doesn't%20work%20with%20closed%20files.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-804253%22%20slang%3D%22es-ES%22%3ERe%3A%20To%20Link%20Excel%20Books%20using%20cells%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-804253%22%20slang%3D%22es-ES%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20perfectly!%20Thank%20you%20very%20much%2C%3C%2FP%3E%3CP%3ECharlie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-804322%22%20slang%3D%22en-US%22%3ERe%3A%20To%20Link%20Excel%20Books%20using%20cells%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-804322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F390822%22%20target%3D%22_blank%22%3E%40cvazquez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECharlie%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
cvazquez
New Contributor

Dear all,

 

I need to link the values of several excel books to a main excel workbook. The process using the copy-paste function with all the excel sheets opened is simple, however, when I try to link the values by hand (using the file domain) it doesn't work. The problem comes from a failed syntax when I replace the original domain between the single quotes with a cell with the "text" of that domain.

 

For example:

-Original

='C:\CVR\Folder1\Folder1-1\Folder1-1-1\[ResultsExcel_3Dv11.xlsx]CV Average Temperature'!$B2

 

-Fail case

='[A1]'!$B2

being the A1 value=C:\CVR\Folder1\Folder1-1\Folder1-1-1\[ResultsExcel_3Dv11.xlsx]CV Average Temperature

 

 

I am trying to do this process because I need to do some changes in the links to the different excel workbooks such the ones that you can see in the attached example table

 

Thank you in advance for your answers and kind regards,

Charlie

 

3 Replies
Solution

@cvazquez 

In general it'll be like

=INDIRECT("'"&A8&"'!$B2")

but the file you linked shall be opened, INDIRECT doesn't work with closed files.

Highlighted

@Sergei Baklan 

 

It works perfectly! Thank you very much,

Charlie

@cvazquez 

Charlie, you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies