SOLVED

How to get data from a file if I select the file name from a list of files?

%3CLINGO-SUB%20id%3D%22lingo-sub-3073827%22%20slang%3D%22en-US%22%3EHow%20to%20get%20data%20from%20a%20file%20if%20I%20select%20the%20file%20name%20from%20a%20list%20of%20files%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073827%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20cell%20K10%20I%20have%20a%20drop%20list%20with%20files%20name%20(source%20is%20column%20C)%20is%20it%20possible%20to%20get%20the%20data%20from%20the%20selected%20file%20in%20the%20drop%20list%3F%26nbsp%3B%20The%20target%20cell%20is%20always%20for%20example%20L10.%20So%20if%20I%20pick%20a%20different%20file%20name%20from%20the%20drop%20list%20I%20get%20the%20value%20of%20c%20ell%20L10%20of%20the%20new%20selected%20file%3F%20if%20it%20is%20possible%20with%20a%20formula%20because%20i%20just%20start%20learning%20VBA%20and%20i%20believe%20it%20might%20be%20a%20bit%20too%20complex%20at%20this%20stage%20to%20use%20VBA%20for%20me%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22olopa67_0-1643287474666.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342934iCC04483C1A50F8F6%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22olopa67_0-1643287474666.png%22%20alt%3D%22olopa67_0-1643287474666.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ethank%20you%20for%20your%20time%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3073827%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-3074146%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20data%20from%20a%20file%20if%20I%20select%20the%20file%20name%20from%20a%20list%20of%20files%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3074146%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1201113%22%20target%3D%22_blank%22%3E%40olopa67%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInstead%20of%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D'%5Bfilename%5Dsheetname'!%24L%2410%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eyou%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDIRECT(%20%22'%5B%22%20%26amp%3B%20K10%20%26amp%3B%20%22%5Dsheetname'!%24L%2410%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3092165%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20data%20from%20a%20file%20if%20I%20select%20the%20file%20name%20from%20a%20list%20of%20files%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092165%22%20slang%3D%22en-US%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%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22olopa67_0-1643634567841.png%22%20style%3D%22width%3A%20731px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343886i15227FF4A8134605%2Fimage-dimensions%2F731x411%3Fv%3Dv2%22%20width%3D%22731%22%20height%3D%22411%22%20role%3D%22button%22%20title%3D%22olopa67_0-1643634567841.png%22%20alt%3D%22olopa67_0-1643634567841.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ethank%20you%20for%20your%20reply%2C%3C%2FP%3E%3CP%3EI%20used%20the%20indirect%20formula%20in%20the%20column%20TARGET%20(G5)%20to%20have%20a%20full%20path%20with%20file%20name%2C%20file%20extension%2C%20work%20sheet%20and%20specific%20cell%20i%20need%20the%20value%20from%2C%20but%20it%20doesn't%20give%20me%20any%20result.%3C%2FP%3E%3CP%3Eit%20works%20in%20the%20cell%20G16%20where%20i%20have%20the%20same%20formula%20you%20can%20see%20in%20F16%20which%20i%20typed%20manually.%3C%2FP%3E%3CP%3Eany%20idea%20why%20is%20not%20working%20%3F%20and%20how%20to%20fix%20it%3F%3C%2FP%3E%3CP%3Ethe%20idea%20is%20to%20have%20later%20on%20the%20column%20A%20as%20a%20drop%20list%20and%20use%20it%20to%20index%20and%20match%20with%20column%20g%20so%20i%20can%20have%20the%20value%20in%20G16%20to%20change%20accordingly.%20thank%20you%20for%20your%20time%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

In cell K10 I have a drop list with files name (source is column C) is it possible to get the data from the selected file in the drop list?  The target cell is always for example L10. So if I pick a different file name from the drop list I get the value of c ell L10 of the new selected file? if it is possible with a formula because i just start learning VBA and i believe it might be a bit too complex at this stage to use VBA for me 

olopa67_0-1643287474666.png

thank you for your time

3 Replies

@olopa67 

Instead of

='[filename]sheetname'!$L$10

you may use

=INDIRECT( "'[" & K10 & "]sheetname'!$L$10

@Sergei Baklan 

olopa67_0-1643634567841.png

thank you for your reply,

I used the indirect formula in the column TARGET (G5) to have a full path with file name, file extension, work sheet and specific cell i need the value from, but it doesn't give me any result.

it works in the cell G16 where i have the same formula you can see in F16 which i typed manually.

any idea why is not working ? and how to fix it?

the idea is to have later on the column A as a drop list and use it to index and match with column g so i can have the value in G16 to change accordingly. thank you for your time 

best response confirmed by olopa67 (Contributor)
Solution

@olopa67 

I guess here

image.png

it shall be ... &"recepe card'!L10" , not .. &"recepe card'!&(L10)