Formula with imported data as a connection only

Copper 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.