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.

Google Online Preview   Download