Workbook Dynamic Links

%3CLINGO-SUB%20id%3D%22lingo-sub-1547580%22%20slang%3D%22en-US%22%3EWorkbook%20Dynamic%20Links%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547580%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20%22Work%20Orders%22%20files%20on%20our%20server%20Z%3A%2C%20they%20are%20all%20named%20by%20%22Job%20Number%22%20i.e.%20106574.%3C%2FP%3E%3CP%3EZ%3A%5CREPORTS%5CWork%20Orders%5C106574.xlsx%3C%2FP%3E%3CP%3EThe%20total%20profit%20or%20loss%20on%20a%20job%20is%20in%20cell%20H52%20in%20all%20cases.%3C%2FP%3E%3CP%3ENOW%2C%20me%20on%20my%20computer%2C%20on%20the%20network%2C%20want%20to%20make%20simply%20monthly%20reports%2C%20in%20EXCEL%20by%20just%20typing%20in%20the%20job%20number%20and%20having%20the%20next%20cell%20populate%20with%20the%20value.%3C%2FP%3E%3CP%3ETHEREFORE%2C%20if%20I%20type%20106574%20in%20cell%20A1%2C%20I%20would%20like%20cell%20A2%20to%20populate%20with%20the%20value%20of%3A%20106574.xlsx%2C%20H52%3C%2FP%3E%3CP%3ESOMETHING%20LIKE%3A%26nbsp%3B%20%3D'Z%3A%5CREPORTS%5CWork%20Orders%5C%5B106574.xlsx%5DSheet1'!H52%3C%2FP%3E%3CP%3EBUT%3A%20For%20some%20reason%2C%20this%20keeps%20taking%20me%20to%20my%20C%3A%20drive%20and%20not%20the%20Z%3A%20drive.%3C%2FP%3E%3CP%3EALSO%3A%20I%20would%20like%20to%20have%20my%20link%20referenced%20to%20cell%20A1..........%3C%2FP%3E%3CP%3E'Z%3A%5CREPORTS%5CWork%20Orders%5C%5BA1.xlsx%5DSheet1'!H52........I%20know%20this%20is%20incorrect%2C%20but%20you%20get%20my%20point.%3C%2FP%3E%3CP%3EINDIRECT%3F%3F%3F%3F%3F%3F%3F%20function%3F%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20out%20there%3F%20Thank%20You%2C%3C%2FP%3E%3CP%3EChris%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1547580%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1547732%22%20slang%3D%22en-US%22%3ERe%3A%20Workbook%20Dynamic%20Links%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547732%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740932%22%20target%3D%22_blank%22%3E%40chrisahlf%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EYou%20are%20correct%2C%20%22%3Dindirect%22%20should%20do%20the%20trick%20for%20you...%3C%2FP%3E%3CP%3E%3DINDIRECT(%22'%3CSPAN%3EZ%3A%5CREPORTS%5CWork%20Orders%5C%3C%2FSPAN%3E%5B%22%26amp%3BA1%26amp%3B%22.xlsx%5DSheet1'!%24H%2452%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBen%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

We have "Work Orders" files on our server Z:, they are all named by "Job Number" i.e. 106574.

Z:\REPORTS\Work Orders\106574.xlsx

The total profit or loss on a job is in cell H52 in all cases.

NOW, me on my computer, on the network, want to make simply monthly reports, in EXCEL by just typing in the job number and having the next cell populate with the value.

THEREFORE, if I type 106574 in cell A1, I would like cell A2 to populate with the value of: 106574.xlsx, H52

SOMETHING LIKE:  ='Z:\REPORTS\Work Orders\[106574.xlsx]Sheet1'!H52

BUT: For some reason, this keeps taking me to my C: drive and not the Z: drive.

ALSO: I would like to have my link referenced to cell A1..........

'Z:\REPORTS\Work Orders\[A1.xlsx]Sheet1'!H52........I know this is incorrect, but you get my point.

INDIRECT??????? function????

 

Any help out there? Thank You,

Chris

1 Reply
Highlighted

Hi @chrisahlf,

You are correct, "=indirect" should do the trick for you...

=INDIRECT("'Z:\REPORTS\Work Orders\["&A1&".xlsx]Sheet1'!$H$52")

 

Ben