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.