I’ve been working with the spreadsheet app in the desktop editor and continue to run into a bug with the VSTACK function. I tried to upload a sample file but can’t as I’m a new user so here is the steps to reproduce the issue.
Create a blank spreadsheet with three tabs (Tracker, Income, and Expense)
Add the following column headers to each tab (Date, Description, Category, Income, Debit)
On the Tracker add an additional column header “Running Balance”
In the income tab add the following sample data:
Date
Description
Category
Income
Debit
2025-01-03
Pay
Income
$ 1,100.00
2025-01-17
Pay
Income
$ 1,000.00
2025-01-31
Pay
Income
$ 1,000.00
2025-02-14
Pay
Income
$ 1,000.00
2025-02-28
Pay
Income
$ 1,000.00
In the Expense tab add the following sample data:
Date
Description
Category
Income
Debit
2025-01-01
Rent
Rent
$ 1,200.00
2025-02-01
Rent
Rent
$ 1,200.00
In the Tracker tab add the following sample data to row 2 under the headers:
Date
Description
Category
Income
Debit
Running Balance
2025-01-01
Starting Balance
$ 500.00
In cell Tracker!A3 add the following formula
=SORT(FILTER(VSTACK(Income!A2:E100,Expense!A2:E100),VSTACK(Income!A2:A100,Expense!A2:A100<>“”)),1)
At this point the data from both the Income and Expense tabs should now display:
Date
Description
Category
Income
Debit
2025-01-01
Starting Balance
2025-01-01
Rent
Rent
$ -
$ 1,200.00
2025-01-03
Pay
Income
$ 1,100.00
$ -
2025-01-17
Pay
Income
$ 1,000.00
$ -
2025-01-31
Pay
Income
$ 1,000.00
$ -
2025-02-01
Rent
Rent
$ -
$ 1,200.00
2025-02-14
Pay
Income
$ 1,000.00
$ -
2025-02-28
Pay
Income
$ 1,000.00
$ -
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
BUG #1: The Filter Function should remove all empty values but instead a number of rows w/ #N/A filling the cells of x number of rows
In cell Tracker!F3 enter the following formula:
=IF(ISNA(A3),“”,F2+D3-E2) and copy down through the rest of the rows and the data should now look like:
Date
Description
Category
Income
Debit
Running Balance
2025-01-01
Starting Balance
$ 500.00
2025-01-01
Rent
Rent
$ -
$ 1,200.00
$ 500.00
2025-01-03
Pay
Income
$ 1,100.00
$ -
$ 400.00
2025-01-17
Pay
Income
$ 1,000.00
$ -
$ 1,400.00
2025-01-31
Pay
Income
$ 1,000.00
$ -
$ 2,400.00
2025-02-01
Rent
Rent
$ -
$ 1,200.00
$ 2,400.00
2025-02-14
Pay
Income
$ 1,000.00
$ -
$ 2,200.00
2025-02-28
Pay
Income
$ 1,000.00
$ -
$ 3,200.00
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
Change the value of cell Expenses!E2 to 1201 (or any other number)
Date
Description
Category
Income
Debit
Running Balance
2025-01-01
Starting Balance
$ 500.00
2025-01-01
Rent
Rent
$ -
$ 1,201.00
#NUM!
2025-01-03
Pay
Income
$ 1,100.00
$ -
#NUM!
2025-01-17
Pay
Income
$ 1,000.00
$ -
#NUM!
2025-01-31
Pay
Income
$ 1,000.00
$ -
#NUM!
2025-02-01
Rent
Rent
$ -
$ 1,200.00
#NUM!
2025-02-14
Pay
Income
$ 1,000.00
$ -
#NUM!
2025-02-28
Pay
Income
$ 1,000.00
$ -
#NUM!
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
BUG #2 Whenever a value is changed in either the Income or Expense tabs the calculated Running Total column now displays #NUM!
To resolve this you place the cursor back in the Tracker!F3 cell and press enter.
Recommended resolution for bugs:
Bug 1: Fix the filter function to only return rows with value when <>“” is applied as the criteria.
Bug 2: Automatically refresh the running totals when values used in a VSTACK are changed.
Thank you for allowing me to upload the file as that makes it much easier. I’ve attached it to the original post and here( SampleVstackIssue.xlsx (12.3 KB)
). I’m using OnlyOffice 8.1.1.27 on PoPOS but the application acted the same on my windows 10 machine.
Thanks for the file. I see the issue with calculation of the formula in column E of sheet Tracker after changing values in column E of sheet Expenses. We will analyze this case.
As for the second issue with VSTACK (BUG #1): it is not quite clear what do you expect. I checked Excel and behavior is the same - all values below row 9 of Tracker sheet are also displayed as #N/A. Please elaborate on this.
As for bug#1 I’ve recreated the file using only Excel (latest version of Office365) and it does not produce the #NUM characters. Below is a screenshot showing the formula is the same (except it has 3 tabs in the VSTACK vice 2. But as shown no #NUM are showing and no filter is appled. The source file is attached below. Hope that helps but let me know if I can provide any other information.
Most recent provided file opens fine via Desktop Editors too, from what I can see. Content is identical to the Excel. Can you point to the exact issue?
Previous file is also displayed similarly to Excel with N/A values. Sorry if I am misunderstanding.
What I see as the issue is that if I create the file using Excel the #NUM issue does not exist. I can then open the file in OnlyOffice and it works. However if I create the file in OnlyOffice then I get the fields populated with #NUM.
What I’d like to see happen is that the file is created, and edited, using OnlyOffice and it not have the #NUM issue. I’d like to not have to do anything with Excel.
Now I see, thanks. So I’ve tried running through provided step-by-step scenario and as of now I am getting #VALUE! error after inserting formula from step 7. What am I doing wrong? Can you double check this scenario or let me know what I did wrong?
Yes, there was a typo and I changed the formula to use correct name of the sheet during my test. Could you please create a file according to your scenario but leave the step with formula behind? Basically, so that I only needed to insert provided formula into cell A3 of Tracker sheet to reproduce the issue. I think it’d be much more productive to have ready-to-go file.
I also have a tab in it called “InconsistentResults”. In that tab is the same table 2x with the same formula but with differing results. This is just further evidence that fundamentally the VSTACK function doesn’t work.
In the first table (Cells A-F) the Expense data doesn’t show up where as it does in the second
table (H-M) using the same formula (copied and pasted not typed in).
I think I found the reason why this formula returned #VALUE! instead of actual values. Quotation marks are different when copying from here and pasting into the editor, it should be copied like that as code:
With that formula I’ve received proper value in A3, however, it does not automatically expands formula to the array. I believe this is behavior you are describing.
I’d like to inform you that in next version VSTACK function will be improved to perform automatic calculation, here as an example with your formula:
