Hello,

Really hoping someone can help me with this... after nearly 4 hours of googling it's 10pm, and I'm ready to give up!

I create DocX templates in Word for Xero for clients. I have been asked if it's possible to add a cumulative balance column to a statement template.

The Xero template I start with has an invoice balance column, so I thought it would be relatively easy to write the formulas, The first cell in the column is balance left. Following that it should be balance above + balance left.

Not so simple! Using SUM(ABOVE) + SUM(LEFT) didn't work because I got the cell above, plus the ones above that... all other variations I've attempted show errors.

There are 2 complicating factors even after I worked out I probably needed to reference specific cells.

1. Xero adds outstanding invoices to the statement. There might be one invoice and one row, or there might be 20. It seems to do this with TableEnd:Line. So I can't see how I can reference the cell above and the cell to the left when I don't know how many there'll be, and I can't find how to add one to the cell reference I used above.

2. Statements can have negative balances mixed into positive ones. Word didn't seem to like that very much! I changed my UK (x.xx) formatting to -x.xx, but I can't tell if that's worked until I can't get the cumulative column to add up correctly!

I'm attaching the Xero standard starting Statement template and where I got to, in case I'm missing something incredibly obvious.

Please don't judge - I'm an accountant - I'm pretty good in Excel. Probably still not as good as most of you 😂🙄

All help gratefully received.


Kate

Attached Files

	Xero Statement Template.docx (26.7 KB, 3 views)
	Where I gave up.docx (32.8 KB, 2 views)

I would have a good look through Xero's available fields because I would have expected there to be a field for the cumulative balance in there. It would be better to get that field value from the source than have to construct it via a formula.

Perhaps you could relate the current balance back to the static value for the opening balance. You can maybe use a static cell reference to the opening balance so the formula could be
{=F2-<<Balance>>}
where F2 is the cell reference for the opening balance and every 'cumulative' balance result is basically the difference between the opening balance and the current balance.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

Hi Guessed,

Thank you very much for replying.

This page lists the available fields Xero has: Xero Central

You would expect «Balance» to be the one needed, since it says "Running total of sales invoices". But nope, that just shows the balance remaining on that invoice! Incredibly annoying.

I've been trying your second suggestion for nearly 2 hours now, without much success. The problem (I think) is that the statement shouldn't ever have an opening balance, so referring to cells above on invoice line one doesn't work.

I've now removed the opening balance row totally.

Do you happen to know what part of the statement is telling Xero how many invoice lines there should be?

Maybe if I can separate the codes for invoice 1 and invoices 2 to n, I can fix invoice 1 and use that as the static value?

Thanks again!

I don't think I have any real insights that are going to actually help you. I think you may need to question the Xero gurus on whatever web forum they congregate on.

I can say that the statement does NOT tell Xero how many invoice lines there are. Your document is a mail merge document that is running a dynamic query on the Xero database and the number of invoice lines depends on how many records in that database match the query parameters.

Basically the mail merge works using a SQL query to gather records out of Xero. Now it is possible to construct a SQL query to return a running total (see this example) so it is conceptually possible to create the necessary field as part of the query you are using but I have no way of testing that without the software. I assume the SQL query is built on the fly by the user clicking a button in Xero so it would need to be sorted there.

If you could run a macro on the file AFTER the rest of the table is produced it would be relatively simple to populate the Cumulative Total column but this is likely not an option you (or your clients) would entertain.

If we were to say that the Opening Balance is always zero then in my non-accounting mind I would assume that Balance and Cumulative Balance would be the same thing.
However you say that «Balance» shows the balance remaining on that invoice. Are you saying this is the same value on every row (ie equal to «TotalDueAmount») or is it the difference between «Amount» and «PaidAmount» just on that row of the table?

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

I emailed Xero! They replied: "right now it's currently not possible to add a running balance column to the Customer Statements. However this is a request that's already been raised with us and you can vote for this request."

Helpful... they also sent me a link to the Microsoft Support website

Thanks very much for the info and links. I am going to delve deeper over the weekend.

I can confirm that the opening balance will always be zero though. Also that you are correct, «Balance» is the difference between «Amount» and «PaidAmount» for each individual invoice or row. Not bad for a non-accountant