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