Quattro Pro Formulas, Functions, and Macros
[Pages:259]Quattro Pro Formulas, Functions, and Macros
Charles M. Cork, III March 27, 2018
Contents
Introduction
i
The reason for this book . . . . . . . . . . . . . . . . . . . . . . . . . . . . i
Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ii
QP quirks, generally . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . iii
I Quattro Pro Formulas
1
1 Basic Formulas
2
Relative v. absolute addresses . . . . . . . . . . . . . . . . . . . . . . . . . 3
Relative ("R1C1") v. normal reference style . . . . . . . . . . . . . . . . . 5
Combining text: Text (string) formulas . . . . . . . . . . . . . . . . . . . . 5
Combining text and the content of cells . . . . . . . . . . . . . . . . . . . 6
How to set up a database showing interest compounding monthly . . . . . 6
How to set monthly installments so that interest payments are equal for
each payment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
How to type the date of every Friday in a year in a column . . . . . . . . . 7
2 Block (Array) Formulas
9
Uses of block formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
How to count or sum all of the numbers in a column that are less than a
certain number . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
How to count all entries in a column that equal the data in this/each row . 11
How to count or sum all cells that meet two conditions . . . . . . . . . . . 12
How to add a running total of all cells, or of only cells meeting a condition 12
How to sum the numbers that come within a date range . . . . . . . . . . 13
How to get the average of numbers in a column that meet a condition . . 14
How to get the maximum and minimum number that meets a condition . 15
How to get the maximum value in a column on a certain date . . . . . . . 15
How to get the minimum value in a column on a certain date . . . . . . . 16
How to mark possible duplicate entries within a time range . . . . . . . . 17
II Quattro Pro Functions
18
3 Conditional functions
20
@IF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
@CHOOSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
i
4 The Properties of a Cell, Page, File, etc.
22
@CELL, @CELLPOINTER, @CELLINDEX . . . . . . . . . . . . . . . . . . . 22
@IS*** logical functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
@TYPE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
@N, @S . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
@PROPERTY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
@COMMAND . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
@FILEEXISTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
How (not) to test whether a cell is blank . . . . . . . . . . . . . . . . . . . 26
How to identify the last non-blank cell in a column . . . . . . . . . . . . . 27
How to enter multiple lines of text in a single cell . . . . . . . . . . . . . . 28
How to determine the width and height of a row in inches . . . . . . . . . 28
Appendix: Cell (Active_Block) Properties . . . . . . . . . . . . . . . . . . . 29
Appendix: Worksheet (Active_Page) Properties . . . . . . . . . . . . . . . 30
Appendix: Notebook Properties . . . . . . . . . . . . . . . . . . . . . . . . 31
Appendix: Application Properties . . . . . . . . . . . . . . . . . . . . . . . 32
5 The Coordinates of a Cell or Block
34
@@ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
@ADDRESS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
@OFFSET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Comparing @ADDRESS and @OFFSET . . . . . . . . . . . . . . . . . . . . 37
How to replace named blocks with @@ . . . . . . . . . . . . . . . . . . . 38
How to get the attributes of a single cell by indirect reference . . . . . . . 38
@BLOCKNAME, @BLOCKNAMES . . . . . . . . . . . . . . . . . . . . . . . 39
6 Functions for Text (Strings)
40
Creating text: @CHAR, @REPEAT . . . . . . . . . . . . . . . . . . . . . . 40
Deleting non-text: @CLEAN, @TRIM . . . . . . . . . . . . . . . . . . . . . 40
Combining text: @CONCATENATE . . . . . . . . . . . . . . . . . . . . . . 40
Dividing text: @LEFT, @RIGHT, @MID, @FIELD . . . . . . . . . . . . . . 41
Modifying or deleting text: @SUBSTITUTE, @REPLACE, @SETSTRING . 41
Modifying the case of text: @UPPER, @LOWER, @PROPER . . . . . . . . 42
Other basic functions: @LENGTH, @FIND . . . . . . . . . . . . . . . . . . 42
How to count the number of instances of a particular character in a cell . . 42
How to count the number of words in a particular cell . . . . . . . . . . . 43
How to return the last word in a series of words . . . . . . . . . . . . . . . 43
How to return the last name in a string, ignoring suffixes . . . . . . . . . . 46
How to generate a new file name based on the current notebook . . . . . 47
7 Math Functions
49
Adding: @SUM, @TOTAL, @SUBTOTAL, @SUMIF . . . . . . . . . . . . . 49
Averaging: @AVG, @PUREAVG . . . . . . . . . . . . . . . . . . . . . . . . 50
Extremes: @MAX, @MIN, @PUREMAX &-MIN, @LARGEST, @SMALLEST 50
Determining relative rank: @RANK . . . . . . . . . . . . . . . . . . . . . . 50
Rounding/Trimming: @ROUND, @INT, @TRUNC . . . . . . . . . . . . . 51
Remainders and Patterns: @MOD . . . . . . . . . . . . . . . . . . . . . . . 52
How to return the difference between two numbers if positive, or zero if
negative . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
How to return the difference between two numbers as a positive number . 52 How to mark the largest value in a column . . . . . . . . . . . . . . . . . . 53 How to compare value changes from last year when this year is not complete 53 How to sum every other number in a column . . . . . . . . . . . . . . . . 54 How to average the preceding three numbers in a column, not counting
blanks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 How to sum the contents of a column if rows or columns are inserted . . . 56
8 Date and Time Functions
58
Current date and time: @TODAY, @NOW . . . . . . . . . . . . . . . . . . 58
Dates and their components: @DATE, @YEAR, @MONTH, @DAY . . . . . 58
Information about a date: @DATEINFO . . . . . . . . . . . . . . . . . . . 59
Days of the week: @WEEKDAY, @WKDAY . . . . . . . . . . . . . . . . . . 60
Comparing dates: @DATEDIF . . . . . . . . . . . . . . . . . . . . . . . . . 60
Other date functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Time and components: @TIME, @HOUR, @MINUTE, @SECOND . . . . . 61
How to display a message after a certain time of the day . . . . . . . . . . 61
How to sum data by year . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
How to sum data by month and year . . . . . . . . . . . . . . . . . . . . . 62
How to determine the Tuesday after or before a date . . . . . . . . . . . . 63
How to set up delivery tables . . . . . . . . . . . . . . . . . . . . . . . . . 64
How to determine the number of Wednesdays in a given month . . . . . . 65
9 Converting between Text and Numbers
68
Converting numbers to generic number-like text: @STRING, @FIXED . . . 68
Converting numbers to currency text: @DOLLAR, @DOLLARTEXT . . . . 69
Other conversions: @FRACTION, @CHAR . . . . . . . . . . . . . . . . . . 70
Converting text to numbers: @VALUE, @DATEVALUE, @TIMEVALUE . . . 70
How to convert a number to left-padded text . . . . . . . . . . . . . . . . 71
How to convert numeric text formatted as #- into usable numbers . . . . . 71
How to convert a number to feet and inches . . . . . . . . . . . . . . . . . 72
How to convert a date number into the day or month as text . . . . . . . . 72
How to convert numeric dates into text dates . . . . . . . . . . . . . . . . 73
How to convert text dates/times into numeric
dates/times . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
How to convert decimal time to hours and minutes . . . . . . . . . . . . . 74
How to convert clock times to seconds . . . . . . . . . . . . . . . . . . . . 74
How to add one hour to a given time . . . . . . . . . . . . . . . . . . . . . 74
How to create a chronologically sortable column from text numbers . . . . 75
Appendix: Custom numeric formats . . . . . . . . . . . . . . . . . . . . . . 76
10 Getting information about and from a database
78
Getting the width and height of a block: @COLS, @ROWS, @SHEETS . . 79
Getting column and row numbers for a block: @COLUMN, @ROW . . . . 79
Column identifiers: @INDEXTOLETTER, @LETTERTOINDEX . . . . . . . 79
Counting (non-)blank cells: @COUNT, @COUNTBLANK . . . . . . . . . . 80
Counting conditionally: @COUNTIF . . . . . . . . . . . . . . . . . . . . . 80
Basic functions for extracting numeric information from a database . . . . 81
Retrieval of last entry: @LASTCELLVALUE . . . . . . . . . . . . . . . . . . 81
Retrieval of parallel value: @LOOKUP . . . . . . . . . . . . . . . . . . . . 81 Retrieval by index value: @VLOOKUP, @HLOOKUP, @VHLOOKUP . . . . 82 Retrieval by column and row: @INDEX, @XINDEX . . . . . . . . . . . . . 83 Comparing the preceding functions . . . . . . . . . . . . . . . . . . . . . . 84 Retrieval by text coordinates: @@ . . . . . . . . . . . . . . . . . . . . . . 84 Determining column/row offset from matches: @MATCH . . . . . . . . . 84 Getting coordinates of extreme values: @MAXLOOKUP, @MINLOOOKUP . 86 How (not) to derive coordinates relative to the current cell . . . . . . . . . 86 How to retrieve data from cells relative to the current cell . . . . . . . . . 89 How to identify the first blank row and cell in a column of data . . . . . . 90 How to get the coordinates of a continuous column of entries . . . . . . . 91 How to count the non-blank cells in the index column of a given block . . 92 How to get the coordinates of a named block . . . . . . . . . . . . . . . . 92 How to get the value in column that is parallel to the largest value in
another column. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 How to get every 10th value in a column . . . . . . . . . . . . . . . . . . . 93 How to get/sum the last five values in a column to which data is added . . 93 How to average the last three numbers, skipping blanks . . . . . . . . . . 94 How to determine if the cellpointer is in a particular block . . . . . . . . . 95 How to determine if a value is in a column or row . . . . . . . . . . . . . . 96 How to mark duplicates in a column . . . . . . . . . . . . . . . . . . . . . 96 How to return the (first) matching row of data in a database . . . . . . . . 97 How to find and return matches after the first one . . . . . . . . . . . . . . 97 How to find the top three scores in a database . . . . . . . . . . . . . . . . 101
III Quattro Pro Macros
105
11 Introduction to Quattro Pro Macros
106
Macro commands and text . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Normal sequence of commands . . . . . . . . . . . . . . . . . . . . . . . . 108
Making commands conditional: {If} . . . . . . . . . . . . . . . . . . . . . 108
Detouring or Breaking: {Cell} and {Branch Cell}, {Quit}, {Return} . . . . 109
Error handling: {OnError} . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Pausing: {?} and {Wait} . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Repetition (Looping) by branching back . . . . . . . . . . . . . . . . . . . 111
Repetition for a specific number of loops: {For} . . . . . . . . . . . . . . . 111
Speed #1 ? Display Options: {WindowsOff} and Redraw . . . . . . . . . . 113
Speed #2 ? Recalculation Options: Manual, {Calc} and {Recalc} . . . . . 115
Speed #3 ? Close other notebooks and minimize use of functions . . . . . 116
Automating startup and shutdown macros . . . . . . . . . . . . . . . . . . 116
Naming cells to expedite launching macros . . . . . . . . . . . . . . . . . 116
Creating toolbar icons and keystroke shortcuts to launch macros . . . . . . 118
Adding command buttons to launch macros . . . . . . . . . . . . . . . . . 118
Troubleshooting ambiguous cell references in macros . . . . . . . . . . . . 118
12 Basic File System Macros
120
Opening and using notebooks: {FileOpen} and {Activate} . . . . . . . . . 120
Saving a QP Notebook: {FileSave}, {FileSaveAll}, and {FileSaveAs} . . . . 121
Closing a QP notebook: {FileClose} and {FileCloseAll} . . . . . . . . . . . 121
13 Navigation and Selection Macros
122
Cursor Keys: {U}, {D}, {L}, {R}, {Home}, {End}, {PgDn}, {PgUp} . . . . 122
Selecting Cells/Blocks: {Shift}, {Goto}, {SelectBlock},
{EditGoto}, etc. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
How to move to the leftmost cell on the row . . . . . . . . . . . . . . . . . 125
How to move to the first blank cell at the bottom of a column of data . . . 125
How to move to the first blank cell at the right of a row of data . . . . . . 125
How to move to the same cell on the next page . . . . . . . . . . . . . . . 125
How to move to the same cell on the prior page . . . . . . . . . . . . . . . 126
How to move to each page successively . . . . . . . . . . . . . . . . . . . . 127
How to round-trip, to move from one cell to another, and then return . . . 127
How to select the current and adjoining cells . . . . . . . . . . . . . . . . 127
How to select the same cells on restarting, after closing the file . . . . . . 128
How to select cells in a different notebook . . . . . . . . . . . . . . . . . . 128
How to select a cell in a given column based on a row number stored in a
particular cell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
How to move to a given value in the index column of a database . . . . . 129
How to test whether the active cell is in a particular sheet . . . . . . . . . 130
14 Interface Macros
131
Sound: {Beep} . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Giving information: {Message} . . . . . . . . . . . . . . . . . . . . . . . . 131
How to see where the message will appear . . . . . . . . . . . . . . . . . . 132
Making basic choices: {Alert} . . . . . . . . . . . . . . . . . . . . . . . . . 132
Getting input: {Get}, {GetNumber}, {GetLabel} . . . . . . . . . . . . . . . 134
Inhibiting input, avoiding crashes . . . . . . . . . . . . . . . . . . . . . . . 134
Pop-up menus: {Menubranch} . . . . . . . . . . . . . . . . . . . . . . . . 135
Displaying information in cells . . . . . . . . . . . . . . . . . . . . . . . . 135
How (not) to display a "Waiting . . . " message . . . . . . . . . . . . . . . . 136
How to display progressing values . . . . . . . . . . . . . . . . . . . . . . 136
How to display a progress bar . . . . . . . . . . . . . . . . . . . . . . . . . 137
How to display a floating (flickering) progress bar . . . . . . . . . . . . . . 138
How to put highlighted text into a cell for user input . . . . . . . . . . . . 139
How to position the display at the end of the macro sequence . . . . . . . 140
How to select blocks by keys other than arrow keys . . . . . . . . . . . . . 140
How to change buttons that run macros . . . . . . . . . . . . . . . . . . . 141
15 Cell Property Macros
142
{GetObjectProperty}, {GetProperty} . . . . . . . . . . . . . . . . . . . . . 142
{SetProperty}, {SetObjectProperty} . . . . . . . . . . . . . . . . . . . . . . 142
How to copy (all) cell formats from one cell to another . . . . . . . . . . . 143
How to copy selected cell properties from one cell to another . . . . . . . 144
How to view the 256 cell colors available by default . . . . . . . . . . . . . 145
How to color/format cells conditionally . . . . . . . . . . . . . . . . . . . 145
How to color/format cells based on conditions in other cells . . . . . . . . 146
How to shade all cells in a block that contain a particular word . . . . . . 147
How to zebra-stripe a block . . . . . . . . . . . . . . . . . . . . . . . . . . 148
16 Cell Content Macros
150
Placing data anywhere: {Let} . . . . . . . . . . . . . . . . . . . . . . . . . 150
Placing data into the current cell: {PutCell}, {PutCell2} . . . . . . . . . . 151
Placing data or formulas into cells or blocks: {PutBlock}, {PutBlock2} . . 152
Putting data from one block into another: {Put} . . . . . . . . . . . . . . . 152
Recreating a block of formatted data: {BlockValues} . . . . . . . . . . . . 153
Recreating a block of data, with options: {BlockCopy} . . . . . . . . . . . 153
Pasting from the clipboard: {EditCopy} and {EditPaste} or {PasteSpecial}
or {PasteFormat} . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Placing data and selected properties with {SetObjectProperty} . . . . . . . 157
Comparison of Data Transfer Commands . . . . . . . . . . . . . . . . . . . 158
Converting numbers to formatted numberlike text: {Contents} . . . . . . 158
Removing data and formats: {Blank}, {EditClear}, {ClearFormats},
{ClearContents} . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Using search and replace in a macro: {Search} . . . . . . . . . . . . . . . 160
How to deal with blanks in the source cells . . . . . . . . . . . . . . . . . . 160
How to transfer data with no formatting except numeric formatting . . . . 161
How to enter in the current cell the number above it plus one . . . . . . . 162
How to edit every cell in a column/block . . . . . . . . . . . . . . . . . . . 162
How to determine if a cell contains a formula . . . . . . . . . . . . . . . . 164
How to override in-cell formatting . . . . . . . . . . . . . . . . . . . . . . 165
17 Date and Time Macros
166
Entering dates with Ctrl+D or a {Putcell} Macro . . . . . . . . . . . . . . 166
How to enter the current time in a cell . . . . . . . . . . . . . . . . . . . . 167
How to display the date and time in a cell . . . . . . . . . . . . . . . . . . 168
How to save the closing date and time with the file . . . . . . . . . . . . . 168
How to run commands only the first time a notebook is opened each day. . 168
How to get time precision . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
18 Databases: Retrieving Information
170
Simple @VLOOKUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Reporting the entire database with {BlockValues} . . . . . . . . . . . . . . 171
Building reports with {For} loops . . . . . . . . . . . . . . . . . . . . . . . 171
How to transpose data from the same row on different pages to the same
page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Building reports with @MATCH . . . . . . . . . . . . . . . . . . . . . . . . 180
Notebook queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
How to formulate search terms for text in the database . . . . . . . . . . . 183
How to replicate a simple notebook query with a For loop . . . . . . . . . 184
How to replicate a notebook query with @Match and a helper column . . 185
19 Databases: Adding or Modifying Data
188
How to add a row of data to the bottom of a database . . . . . . . . . . . 188
How to add data between the end of the data and totaling functions . . . 189
How to add data with {Form} . . . . . . . . . . . . . . . . . . . . . . . . . 190
How to automate cursor movement during manual data entry . . . . . . . 191
Same, with prompts, default responses, and error checking . . . . . . . . . 191
How to update a row or a cell in a database . . . . . . . . . . . . . . . . . 194
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- quattro pro formulas functions and macros
- nested ifs if and if or
- excel data analysis training e dat
- summarize and analyze data ms excel 2016
- microsoft excel 2013 king county library system
- microsoft excel advanced towson university
- countif function in excel syntax and usage
- one on one computer help computer classes techtutor
Related searches
- excel functions and formulas pdf
- marketing functions and roles
- absolute value functions and translations quizlet
- managerial functions and roles
- manager functions and responsibilities
- ms excel functions and formulas
- management roles functions and skills
- functions and function notation
- excel formulas right and left
- vba and macros pdf
- iphone 11 pro max tips and tricks
- excel formulas lookup and match