SOLVED
Home

File route with variable

%3CLINGO-SUB%20id%3D%22lingo-sub-755849%22%20slang%3D%22en-US%22%3EFile%20route%20with%20variable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-755849%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20way%20to%20insert%20a%20variable%20into%20a%20route%20file%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%E2%80%99m%20using%20index%20function%20with%20match%20function%20to%20find%20values%20from%20a%20different%20file.%3CBR%20%2F%3EAlthough%20the%20data%20needs%20to%20be%20searched%20in%20different%20worksheets%20depending%20on%20the%20value%20of%20a%20cell.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20way%20to%20specify%20with%20a%20cell%20in%20which%20worksheet%20it%20is%20supposed%20to%20look%20for%20the%20data%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-755849%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-755899%22%20slang%3D%22en-US%22%3ERe%3A%20File%20route%20with%20variable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-755899%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376745%22%20target%3D%22_blank%22%3E%40Michael_EVB%3C%2FA%3E%26nbsp%3BYou%20can%20try%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FINDIRECT-function-474B3A3A-8A26-4F44-B491-92B6306FA261%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EINDIRECT%3C%2FA%3E%2C%20but%20it%20requires%20the%20source%20workbook%20to%20be%20open.%20If%20you'll%20be%20working%20with%20closed%20workbooks%2C%20you%20can%20download%20the%20MoreFunc%20add-in%20and%20use%20INDIRECT.EXT.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765302%22%20slang%3D%22en-US%22%3ERe%3A%20File%20route%20with%20variable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765302%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F10827%22%20target%3D%22_blank%22%3E%40Smitty%20Smith%3C%2FA%3Ethank%20you%20for%20your%20response.%3CBR%20%2F%3E%3CBR%20%2F%3ECould%20you%20give%20me%20a%20short%20example%20of%20the%20function%20so%20I%20can%20better%20understand%20it%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765312%22%20slang%3D%22en-US%22%3ERe%3A%20File%20route%20with%20variable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765312%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376745%22%20target%3D%22_blank%22%3E%40Michael_EVB%3C%2FA%3E%26nbsp%3BHere%20you%20go.%20In%20cell%20A1%2C%20I%20have%20a%20reference%20to%20an%20external%20workbook%20that's%20open.%20In%20cell%20B1%2C%20I%20have%20INDIRECT(%22'%22%26amp%3BA1)%2C%20which%20returns%20the%20value%20in%20the%20external%20workbook.%20If%20you%20close%20the%20source%20workbook%2C%20then%20the%20function%20will%20return%20a%20%23REF!%20error%2C%20which%20is%20why%20you%20need%20to%20use%20INDIRECT.EXT%20from%20the%20add-in.%20If%20the%20source%20workbook%20will%20always%20be%20open%20alongside%20the%20destination%2C%20then%20no%20problem.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20%22'%22%20part%20of%20the%20formula%20is%20to%20add%20back%20the%20apostrophe%20that%20should%20be%20at%20the%20beginning%20of%20the%20file%20path.%20It%20is%20there%20in%20cell%20A1%2C%20but%20Excel%20sees%20it%20as%20a%20non-printing%20character.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20416px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F123876i24F1386E10A5D643%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22INDIRECT.jpg%22%20title%3D%22INDIRECT.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Michael_EVB
New Contributor
Hello,

Is there a way to insert a variable into a route file?

I’m using index function with match function to find values from a different file.
Although the data needs to be searched in different worksheets depending on the value of a cell.

Is there a way to specify with a cell in which worksheet it is supposed to look for the data?
3 Replies

@Michael_EVB You can try INDIRECT, but it requires the source workbook to be open. If you'll be working with closed workbooks, you can download the MoreFunc add-in and use INDIRECT.EXT.

@Smitty Smith thank you for your response.

Could you give me a short example of the function so I can better understand it?
Solution

@Michael_EVB Here you go. In cell A1, I have a reference to an external workbook that's open. In cell B1, I have INDIRECT("'"&A1), which returns the value in the external workbook. If you close the source workbook, then the function will return a #REF! error, which is why you need to use INDIRECT.EXT from the add-in. If the source workbook will always be open alongside the destination, then no problem.

 

The "'" part of the formula is to add back the apostrophe that should be at the beginning of the file path. It is there in cell A1, but Excel sees it as a non-printing character. 

 

INDIRECT.jpg

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 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
29 Replies