SOLVED

help with INDIRECT formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3092164%22%20slang%3D%22en-US%22%3Ehelp%20with%20INDIRECT%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092164%22%20slang%3D%22en-US%22%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%20a%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%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%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3092164%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3092218%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20INDIRECT%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092218%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%3EThere%20are%20several%20problems%20with%20the%20formula%2C%20but%20it%20doesn't%20really%20matter.%20The%20INDIRECT%20function%20only%20works%20with%20references%20to%20another%20workbook%20if%20that%20workbook%20is%20open%20in%20Excel.%20If%20the%20other%20workbook%20is%20closed%2C%20INDIRECT%20will%20return%20%23REF!%3C%2FP%3E%0A%3CP%3EI'd%20import%20the%20sheets%20you%20need%20into%20the%20current%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3092220%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20INDIRECT%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092220%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%3EIt%20looks%20like%20duplication%20of%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fhow-to-get-data-from-a-file-if-i-select-the-file-name-from-a%2Fm-p%2F3092165%2Fhighlight%2Ffalse%23M132143%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fhow-to-get-data-from-a-file-if-i-select-the-file-name-from-a%2Fm-p%2F3092165%2Fhighlight%2Ffalse%23M132143%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I used the indirect formula in the column TARGET (G5) to have a full path with file name, file extension, work sheet and a 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 

olopa67_0-1643634567841.png

2 Replies

@olopa67 

There are several problems with the formula, but it doesn't really matter. The INDIRECT function only works with references to another workbook if that workbook is open in Excel. If the other workbook is closed, INDIRECT will return #REF!

I'd import the sheets you need into the current workbook.

best response confirmed by olopa67 (Contributor)