Skip to main content

Notifications

Business Central forum
Suggested answer

Formula in Excel linking directly to trial balance data in Dynamics 365

Posted on by
Hello,
 
I am looking for a formula/add-in to pull an account balance from Dynamics 365 just by telling the formula a few criteria. 
I'm not really sure where to start with this, but what I am going toward is an accounting software I used as an auditor that would let you type in a formula that would give you a live value of the balance stored on the software's cloud. It was very useful as you would just have to build all of your spreadsheets with the account numbers and a date at the top and the formula would fill in the balances using those two criteria.
The formula would look somehtign like this: =ABC.AccountBalance(Account,Date) and it would return the balance of that account on that date. Other formulas were things like =ABC.AccountDescription or =ABC.AccountNetChange etc...
I would think this would be developed by now for Dynamics as an add-in, but I can't find it yet.
In the meantime, perhaps I can find a way to link to a data table in Dynamics and somehow reference values with web addresses like this: https://dynamicsenvironment/datatable/accountnumber or something like that and I can just use a concate function to put the link together.
As a last result I'm sure I can just use the edit in excel functionality and just have the trial balance in the back of all of my workbooks, or in a workbook on it's own, but I foresee this breaking a lot, or needing manually refreshed all of the time. Also I think the date would be hard to update for the balance you wanted.
 
Any and all ideas are welcome at this point, thank you for reading.
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 68,095 Super User 2024 Season 2 on at
    Formula in Excel linking directly to trial balance data in Dynamics 365
    Hi, what about using Excel Layout?
    Or Excel Organization Data Types Gallery
     
     
    Hope this can give you some hints.
    Thanks.
    ZHU

Helpful resources

Quick Links

Dynamics 365 Community Update

Welcome to the inaugural Community Platform Update. As part of our commitment to…

Dynamics 365 Community Newsletter - August 2024

Catch up on the latest D365 Community news

Community Spotlight of the Month

Kudos to Mohana Yadav!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,115 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 227,971 Super User 2024 Season 2

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans