Technical > List of Examples
Name Ranges
- Named Ranges as variables -- See ZAP Part A, home page. Make note that the named range should only refer to a single cell in the case of merge cells.
Links
- Links to worksheets w/in a gsheet - See ZAP Part A, home page.
- Links to URLs -- See Personal Organizer page on Spreadsheets-for-all (not there yet, coming soon)
- Links to Named Ranges -- right click on cell and give it at try...
Data Validation for Drop down
- Data Validation for Select List from full column of data - See ZAP Part A, Chart of Accounts (account type) and _Validation Lists tab
- Data Validation for limited data set with corresponding lookup values -- See ZAP Part A, Transaction report, Sort By field ID and functionality
Red Rows for Equations and Dummy Rows with a purpose
- RED dummy row to mark a row where arrayformula equations reside - See ZAP Part A, Ledgers and look for darker red cells
- RED dummy row to avoid data block reference shifting on row insert -- See ZAP Part A, Chart of Accounts.See Part A, checking/cc ledgers for Red dummy row for same reason with additional arrayformula equations in some cells
- RED dummy row to manage arrayformula over a limited vertical range - See ZAP Part A, reconciliation
- RED dummy row to manage conditional row color formatting - See ZAP Part A, _rawTxns tab
Arrayformula
- Arrayformula for inserting constant if another cell is populated -- See ZAP Part A, checking/cc ledgers
- Arrayformula for conditional math -- See ZAP Part A, Txn Aggregator
- Arrayformula formula for vlookup -- See ZAP Part A, Txn Aggregator
- Arrayformula for calculating Running Balance without circular ref error - See ZAP Part A, checking/cc ledgers. This is not intuitive. Great use of row() function. Found on blog post of another.
- Arrayformula for vlookup with dates and ledgers for Bal Sheet type data - See ZAP Part A, Reconciliation
- Arrayformula with limited vertical scope and boundary rows top and bottom - See ZAP Part A, Reconciliation
- Arrayformula with row() function for creating automatic index for data that will not be sorted -- See _rawTxns tabs. this only works for unique id as long as no one sorts this data set as the ID does not move with the rest of the row during a sort.
Simple Arrays
- Simple Arrays with tab and range refs -- See ZAP txn aggregator -- These were initially used a great deal with the Chart of Accounts, _validation lists, and prior to switching most to a query statement to avoid the need to specify order and length of various types of accounts. Best place to see them now are in the txn aggregator union statement.
Unions with Simple Arrays
- Union of data -- comparable to a SQL union command via simple array/object -- See ZAP Txn Aggregator A3. This also includes a blank row in the union for offsetting start of data on the row down from the equation. Super powerful and simple
Database Style Data Reporting w Dynamic SQL Statements based on drop down style user inputs
- Query by Dates and/or text fields and specify sort field and order - See ZAP Transaction Search
- User Input 'tweeking' - See ZAP Part A, Income Statement and By month for ZID value
Gsheets Query Function
- Use Gsheets Query Statement to get data subsets - See ZAP Non-inc/exp txns, Income Statement, Income Statement by Month, _ValidationLists and more...
Gsheets Sumifs Function
- Use gSheets sumifs function for financial reporting - See ZAP Income statement
Gsheets built in Filtering
- Gsheets built in Filtering for full data sheet - See ZAP ledgers. Gsheets has very powerful built in filtering. It has some nuances you must understand
- Gsheets built in Filtering for partial/mid page data set - See ZAP Non-Inc/Exp Txns
ShowHide Functionality with the use of Scripts
- ShowHide rows/columns using Named Ranges -- See ZAP Part A, zids on most sheets other than zid sheet
- ShowHide Sheets/Tabs via scripts - See ZAP Part A zids