I’m using an ODBC driver and connected our myob accountright company files to excel.
I have created another tab to summarise data and used a simple link to the reference cell of the ODBC connection i.e =’ODBC ABC’!B413
The trouble I have is that, if another account is added to the myob company file, the list of accounts extend when I refresh the connection. When this happens =’ODBC ABC’!B413 is no longer referencing to B413, but to B414, thus calculation on the summary sheet are incorrect.
Did anyone on here come across an similar issue? I cannot use the GETPIVOTDATA formula, because the reference table is not a pivot table.
Any help would be greatly appreciated.
Thank you in advance.
Martin
Advertisement
Answer
it would depend on how you summarize your data, if you are performing calculations on your loaded ODBC data using cell references that as you pointed out it might not work out the best. I suggest creating a pivot table in another sheet to summarize your data and performing the calculations on that same sheet from that pivot table, then you can also use GETPIVOTDATA.
If you rather keep using formulas instead, maybe use VLOOKUP() or SUMIFS() to perform some basic calculations, would need to see more examples of what you would need.