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.
Sample File:
SampleVstackIssue.xlsx (12.5 KB)
