Remove a Closed account from Balances sheet without losing historical data

Google Sheets Show & Tell
, ,
10

Here is an extension of this solution for the Net Worth sheet.

People often want to Hide accounts from the Net Worth report for time periods after the account was closed, but still want them visible for when the account was open.

This uses the Accounts sheet Last Update date for the Closed date.
The Last Update date can be changed by changing the Balance History for the closed account.

Change Net Worth sheet cell AC7 to add the following to the QUERY’s WHERE clause:

AND ("&AX23&" <> 'Closed' OR ("&AX23&" = 'Closed' AND "&AX17&" >= date '"&TEXT($AI$2, "yyyy-mm-dd")&"'))

Where:
AX17 = Last Update date (aka Closed date for closed account)
AI2 = Net Worth report starting date range

Here are the before/after Net Worth sheet cell AC7 formulas:
BEFORE:

=query(indirect(AX24),"SELECT "&AX21&", "&W4&", "&AX13&", "&AX14&", "&AX15&" WHERE "&AX13&" <> '' AND "&AX23&" <> 'Hide' ORDER BY "&AX21&", "&W4&", "&AX14,-1)

AFTER:

=QUERY(INDIRECT(AX24),
"SELECT "&AX21&", "&W4&", "&AX13&", "&AX14&", "&AX15&"
 WHERE "&AX13&" <> ''
   AND "&AX23&" <> 'Hide'
   AND ("&AX23&" <> 'Closed' OR ("&AX23&" = 'Closed' AND "&AX17&" >= date '"&TEXT($AI$2, "yyyy-mm-dd")&"'))
 ORDER BY "&AX21&", "&W4&", "&AX14
,-1)

Where I have changed the formula format for better readability.

3 Likes