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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies