Home

Formula with imported data as a connection only

%3CLINGO-SUB%20id%3D%22lingo-sub-876557%22%20slang%3D%22en-US%22%3EFormula%20with%20imported%20data%20as%20a%20connection%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876557%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20create%20a%20formula%20which%20refers%20to%20imported%20data%20(from%20Dynamics%20365).%20The%20external%20data%20has%20been%20imported%20with%20%22only%20create%20connection%22%20and%20added%20to%20the%20data%20model.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20I%20type%20the%20account%20number%20for%20a%20customer%20in%20cell%20A1%20i%20need%20to%20create%20a%20formula%20that%20matches%20the%20customers%20name.%3CBR%20%2F%3EI%20have%20done%20this%20in%20the%20past%2C%20whereby%20the%20data%20was%20placed%20in%20a%20new%20worksheet%20and%20then%20I%20could%20easily%20use%20the%20following%20formula%3A%20%3DINDEX(Account!A%3AA%3BMATCH(A1%3BAccounts!B%3AB%3B0))%2C%20whereby%20%22Account%22%20was%20the%20imported%20worksheet%20with%20all%20the%20data.%3C%2FP%3E%3CP%3EThe%20problem%20I%20have%20is%20that%20I%20can't%20refer%20to%20a%20worksheet%20in%20the%20formula%2C%20since%20the%20data%20is%20only%20a%20connection.%3CBR%20%2F%3EAny%20help%20would%20be%20highly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-876557%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-876628%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20imported%20data%20as%20a%20connection%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876628%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415566%22%20target%3D%22_blank%22%3E%40Oldenbeuving%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can't%20pickup%20by%20formula%20value%20from%20data%20model.%20You%20may%20build%20combined%20table%20by%20Power%20Query%20or%20directly%20in%20data%20model%20and%20land%20it%20back%20to%20Excel%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-876720%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20imported%20data%20as%20a%20connection%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876720%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%2C%20thank%20you%20for%20your%20reply.%3C%2FP%3E%3CP%3EI%20will%20then%20load%20the%20data%20in%20to%20a%20new%20worksheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-876750%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20imported%20data%20as%20a%20connection%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415566%22%20target%3D%22_blank%22%3E%40Oldenbeuving%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eyes%2C%20that's%20also%20a%20variant.%20To%20save%20space%20and%20reduce%20calculation%20time%20you%20may%20load%20only%20columns%20which%20you%20need%20to%20build%20aggregation%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Oldenbeuving
New Contributor

I would like to create a formula which refers to imported data (from Dynamics 365). The external data has been imported with "only create connection" and added to the data model.

 

So if I type the account number for a customer in cell A1 i need to create a formula that matches the customers name.
I have done this in the past, whereby the data was placed in a new worksheet and then I could easily use the following formula: =INDEX(Account!A:A;MATCH(A1;Accounts!B:B;0)), whereby "Account" was the imported worksheet with all the data.

The problem I have is that I can't refer to a worksheet in the formula, since the data is only a connection.
Any help would be highly appreciated.

3 Replies

@Oldenbeuving 

You can't pickup by formula value from data model. You may build combined table by Power Query or directly in data model and land it back to Excel sheet.

@Sergei Baklan, thank you for your reply.

I will then load the data in to a new worksheet.

@Oldenbeuving 

yes, that's also a variant. To save space and reduce calculation time you may load only columns which you need to build aggregation table.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 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
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies