|
Professional's Guide to Robust Spreadsheets With Examples in Lotus 1-2-3 and Microsoft Excel Ronny Richardson 1996 | 301 pages ISBN: 013262320X |
|||
| $29.95 | Softbound print book | Out of print (?) | |
Index
Symbols#VALUE! 232
\Ø 32
Numerics
2D worksheets xix, 13-15
ad-hoc arrangement 14
left-to-right 14
top-to-bottom 14
3D worksheets xix, 16-19, 52
navigating using 16
A
abbreviation
level of significance 63
abbreviations in range names 196-201
absolute address 51
absolute references 51
ad-hoc arrangement 11, 13, 14
add-in program 5, 33
adding charts 240
adding color 70-71
adding columns 14
adding rows 14
address
absolute 51
mixed 51
relative 51
adjusting column width 14
adjusting row height 14
alignment of data 68
alignment options 69
altering Lotus menu 128, 237, 246
application object
Excel 138
argument 115
arranging worksheets 10-19
ad-hoc 11, 13
left-to-right 11
top-to-bottom 10, 11-12
assumptions
hardwired 48-49
major 185
variable 48-49
attributes, worksheet 20
ease-of-use features 20
error-checking 20
external documentation 20
input checking 20
input prompting 20
internal documentation 20
audience, intended 19-20
frequent use by author 19
frequent use by someone else 19
frequent use with modification by someone else 20
infrequent use by author 19
infrequent use by someone else 19
infrequent use with modification by someone else 19
one-time use by author 19
auditing 169
Excel
dependent cells 172-174
errors 175
formulas 177-178
notes 175
precedent cells 172-174
Lotus 170-172
All Formulas 170
Cell Dependents 171
Circular References 171
DDE Links 172
File Links 172
Formula Precedents 171
Report at Range 170
Selection 170
auditing toolbar
Excel 175
auditing tools
backward trace 180
Excel 172-175
forward trace 180
Lotus 170-172
auditing worksheets 169-175
author xviii
Auto-Open 32
AUTO123.WK4 32
automatic charting 244
automatic recalculation 27-29, 53-54
automatically executed macro 32-34, 239
log-on information 34
reasons to use 32
start-up screen 33-34
verifying the environment 33
automating repetitive tasks 106
B
background colors 96
backward trace 180
batch entry 42, 59, 211, 243, 250
built-in 227
macro-based 227
batch entry problem 219-220
batch processing 42, 211
BIO-HELP.WK4 238
BIO-MENU.WK4 238
biorhythms 236
BIORYT.XLS 248
BIORYTHM.WK4 235, 238-247
BIORYTHM.XLS 248-249
BORDER-2.WK4 240
BORDER.WK4 240
borders 71-72, 240
BREAK-1.WK4 240-241, 247
BREAK-1.XLS 250, 255
BREAK-2.WK4 241
BREAK-2.XLS 250
BREAK-3.WK4 235, 241-243, 247
BREAK-3.XLS 250-253, 255
breakpoints
Excel 153-154
bug 128-129
bulletproof
input section 45
bulletproofing a program 23
button
on-screen
Excel 151
C
calculations
defining 2, 4
protecting 55-56
CAR.WK4 243
cell address
absolute 51
mixed 51
relative 51
Cell Dependents 171
cell linking 53-54
cell protection 55-56
cells
dependent 172-174
precedent 172-174
protected 26
unprotected 26
changes
documenting 167
changing tab colors
Lotus 18
changing tab names
Excel 18
Lotus 18
CHAR 229
CHART-1.WK4 244
CHART-1.XLS 253
charting
automatic 244
charts 79-84
adding 240
Excel 83-84
Lotus 80-82
dynamic 82
charts, using to spot errors 244
ChartWizard
Excel 83-84
Circular References 171
class of objects
Excel 139
clip art 96
Clipboard 88
CODE 230
code
reusable 53
code-oriented programming 137
code pane
Excel 154
collection
Excel 139
column A: numbers 25-26
column width 66-67
adjusting 14
column widths for slides 96
columns
adding 14
deleting 14
inserting 14
common compose sequences 77
communicating with user 24
compose key 76
compose sequence 76,77-80
Compose sequences for common formatting operations 78
constant 140
container
Excel 138
container document 85, 88
COP (code-oriented programming) 137
copy and paste 85-86
copying formatting 72-73
copying formulas 165-166
crash
program 129
crash, program.See bulletproofing a program
cursor in input area 193-194
custom dialog box 244
Lotus 126, 236
lotus 236
custom menu 246
D
data
alignment 68
defining 2
sharing 52-53
data entry 243
data, getting from user 23
data requirements 2
data table 212
data verification 195
DATE 230
DDE (Direct Data Exchange) 113
DDE Links 172
debugging 129
debugging macros
Excel 152-155
Lotus 128-131
declaring a variable
Excel 145
default value 27
defining calculations 2, 4
defining inputs 3
defining outputs 1, 2-3
deleting columns 14
deleting rows 14
dependent cells 172-174
dependent relationships
Excel 172-174
diagonal arrangement 14-15, 16
diagram, system 2
dialog box
Excel
custom 150-151
predefined 149
Lotus 123
built-in 123-126
custom 126, 236, 244
modeless 127
DIALOG.DLG 235, 236-237
DIALOG.WK4 236, 244
dictionary of labels 193
display important log-on messages 24
documentation
external 20, 184
internal 20, 186
documenting changes 167
documenting formulas 186-187
documenting macros 187
double-checking totals 163-165, 244
E
ease-of-use features 20
embedding 85-90
embedding, linking and 5
ERR 230
error message 41-42
error message formula
error messages 41
warning messages 41
error prevention 106
ERROR-1.WK4 244
error-checking 20, 27-29
error-resistant worksheets 162
errors 159
copying formulas 165-166
Excel 175
logic 153
runtime 152
syntax 152
formulas 165
range
referencing the wrong one 162-165
simple data 163-165
worksheet 160, 162
errors, anticipating invalid inputs 30
errors table, possible 38, 39
Excel files 247-255
executive summary 65, 184-185, 235
existing forms 193
external documentation 20, 184, 235
executive summary 184-185
formulas 186
logical 186
macros, table of 185
major assumptions 185
range names, table of 185
sample inputs and outputs 186
external monitor 91
extreme data 168-169
F
file links 172
filter user inputs 194
Format Painter 72
formatting
adding color 70-71
alignment of data 68
alignment options 69
borders 71-72, 240
column width 14, 66-67
Compose sequences for common formatting operations 78
copying 72-73
fonts and numbers 67-68
Format Painter 72
informational screens 70
input section 70
numbers 67-68
rotated text 69
row height 14
text wrap 69
TipWizard 74
formatting standards 196-197
forms
matching existing 24
formula precedents 171
formulas 186
auditing 177-178
copying 165-166
documenting 186-187
Formulas, All 170
forward trace 180
frames 96
G
GAS-1.WK4 244
GAS-2.WK4 244
GAS-3.WK4 244
group mode
Excel 97
Lotus 97
group mode warning
Excel 19
Lotus 18
H
hardwired assumptions 48-49
hardwiring xviii, 185
HEATPUMP.WK4 244-245
HEATPUMP.XLS 253
Help screens 110
history 10
home page, author's xx
hyperspace 132
I
IF 231
IF statements
nested 38
immediate pane
Excel 155
information, getting from user 23-24
informational screens
formatting 70
input 9
verifying user 23
input checking 20
input prompting 20
input section 23
bulletproofing 23, 45
formatting 70
input section components
automatic recalculation 27-29
column A: numbers 25-26
column B: the questions 26
column C: the answers 26
column D: messages: communicating with user 26-29
column E: values actually used 29-31
default value 27
documentation 25
error-checking formula 27
invalid inputs 27
processing section 27
suspect inputs 27
valid inputs 27
input standards 193-195
cursor in input area 193-194
existing forms 193
filter user inputs 194
messages in column C 194
numbers in column A 194
prompts in column B 194
range names 194
top left corner 193
use protection 194
values used in column D 194
visual 193
inputs
defining 3
invalid 27, 30
suspect 27
valid 27
inserting columns 14
inserting rows 14
INT 231
internal documentation 20, 186
documenting formulas 186-187
macro 187
Internet
author's home page xx
invalid inputs 27, 30
L
labels
dictionary of 193
standards 192-193
left-to-right arrangement 11, 12-13, 14, 16, 245
level of significance 63
LFT-RGT.WK4 245
link, OLE 5
link results 253-254
LINK.DOC 235, 236-237, 245, 254
LINK.WK4 236-237, 245, 247, 254
LINK.XLS 253-254
linking 85-90, 245
cell 53-54
Clipboard 88
container document 88
spreadsheet 51-55
linking and embedding, object 5
LMBCS 75-76, 229, 230
LMBCS characters 246
LMBCS.WK4 246
log on information 34
log-on messages, displaying 24
logic errors
Excel 153
logical documentation 186
lookup table 215-217
"looping" macro 212
Lotus
opening screen, showing 238
Lotus menu
altering 237
replacing 237
Lotus Multibyte Character Set 75-76, 229, 230
M
macro 10, 105, 254
\Ø 32
argument 115
Auto-Open 32
automatically executed 32-34
automating repetitive tasks 106
debugging
Excel 152-155
Lotus 128-131
Single Step mode 129-130
Trace mode 129
development process 107-108
documenting 187
error prevention 106
Excel 137-155
adding 219
immediate pane 155
logic errors 153
runtime errors 152
stepping 154-155
syntax errors 152
help system 122-123
hyperspace 132
"looping" 212
Lotus 108-137
adding 217-218
Help screens 110
range names 110
recorder 112
recording 111-113
three-column formatting 109
Transcript window 111-112
performing tricky tasks 106
recording 111-113
running from a menu 116
macro button 247, 255
creating 121-122
modifying 122
macro categories
Lotus
chart 113
data manipulation 113
database 113
DDE 113
Direct Data Exchange 113
edit 114
file 114
flow-of-control 114
keystroke equivalents 114
navigation 114
object linking and embedding 113
range 114
solver 114
style 114
text file manipulation 114
tools 114
user environment 114
Version Manager 114
window and screen display 114
macro management 132-137
macro menus
Excel 151-152
macro section 180
macro viruses 155
MACRO1.XLS 254-255
MACRONAM.WK4 246
macros, table of 185
major assumptions 185
matching existing forms 24
MAX 231
menu object
Excel 138
menus 6
Lotus
altering 128, 246
custom 246
replacing 128, 246
macro
Excel 151-152
stand-alone 116-120
stand-alone nested 120-121
messages
displaying, log-on 24
error 41-42
error message formula 41
warning 29-30, 41
messages in column C 194
method 140
MIN 232
mixed address 51
model xviii.See also spreadsheet, working with someone else's 175-181
model modifications 189
MORTGAGE.XLS 255
N
named styles 73-75
navigating using 3D worksheets 16
Navigator [Name Box] 15
nested IF statements 38
NEWNAME.WK4 246
notes
Excel 175
NOW 232
numbers
formatting 67-68
O
object
Excel
application 138
class of 139
collection 139
menu 138
range 138
range class 139
read/write properties 140
sheet 138, 139
workbook 138
method 140-141
property 139
object linking and embedding 5, 85-90
Clipboard 88
container document 85, 88
copy and paste 85-86
linking 88-90
object 85
source document 85
objects 85, 137-139
OLE 5, 85-90
OLE (object linking and embedding) 113
OLE link 5
OOP (object-oriented programming) 137-139
opening screen, showing 238
operation precedence 48
organizational standards 192-193
output 9-10
output standards 197-199
outputs
defining 1, 2-3
OWN-MENU.WK4 246
P
pages.See sheets
Paint Brush 99
password protected 33
PERCENT.WK4 247
performing tricky tasks 106
planning 1
positioning text 96
possible errors table 38, 39
precedence 47-48
precedent cells 172-174
precedent relationships
Excel
indirect 172-174
predefined dialog box
Excel 149
presentations 91-92
background colors 96
clip art 96
external monitor 91
frames 96
group mode
Excel 97
Lotus 97
Paint Brush 99
positioning text 96
projection panel 91
screen show 93
slide show 247, 255
slide types
charts 94
numbers 94
text 93
titles 93
slides 93-94
video projector 91
printing
report 63
processing 9
batch 42
processing section 27, 45, 180
processing standards 195-197
program
add-in 5, 33
utility 5
programmer xviii
projection panel 91
prompts 41-42
prompts in column B 194
properties
Excel
read 139-140
property 139
protected cells 26
protecting calculations 55-56
R
range
referencing the wrong one 162-165
range class
Excel 139
range names 15, 110, 194
abbreviations in 196-201
table 185
using 49-50
range names and absolute references in links 196-201
range object
Excel 138
read properties
Excel 139-140
read/write properties
Excel 140
recalculation
automatic 27-29, 53-54
recording a macro 111-113
references
absolute 51
circular 171
relative address 51
replacing Lotus menu 128, 237, 246
report
executive summary 65
level of significance 63
Report at Range 170
report printing 63
reusable code 53
rewriting worksheets 13
rotated text 69
row height 67
adjusting 14
rows
adding 14
deleting 14
inserting 14
running macros from a menu 116
runtime errors
Excel 152
S
sample inputs and outputs 186
Scenario Manager 255
Excel 226-227
scenarios
Excel 225-227
Lotus 222
standard test 167-168
screen show 93
selection 170
setting column width 66-67
sharing data 52-53
sheet objects
Excel 138, 139
sheets xix
simple data 163-165
single step mode 129-130
slide show 247, 255
slide types
charts 94
numbers 94
text 93
titles 93
slides 93-94
SLIDES.WK4 247
SLIDES.XLS 254, 255
SmartMaster File 135-137
software depository 188
software standards 5, 5-6
source document 85
special characters
Excel 78
LMBCS (Lotus Multibyte Character Set) 75-76
Lotus
compose key 76
compose sequences 77-80
specify full path to links 196-201
spreadsheet xviii
author xviii
programmer xviii
strategic purpose 1
three-dimensional xix
two-dimensional worksheet xix
spreadsheet engine 159
spreadsheet linking 51-55
spreadsheet model designing 1-2
spreadsheet standards 5
spreadsheets 204-205.See also worksheets
standards
formatting 196-197
input 193-195
labels 192-193
organizational 192-193
output 197-199
processing 195-197
range names 192
software 5, 5-6
spreadsheet 5
technical 196
abbreviations in range names 196-201
range names and absolute references in links 196-201
specify full path to links 196-201
user 5, 6
worksheet 5, 6
start-up screen 33-34
statement 145
stepping
Excel 154-155
strategic purpose 2
styles
named 73-75
subroutines 205-206
SUM 232
summary information 61
summary pages 61
suspect inputs 27
synergy 189
syntax errors
Excel 152
system diagram 2
developing 4
T
tab colors
changing 18
tab names
changing 18
tabs xix, 18
technical standards 196
abbreviations in range names 196-201
range names and absolute references in links 196-201
specify full path to links 196-201
testing
extreme data, with 168-169
scenarios 167-168
text
rotated 69
wrap 69
three-dimensional worksheets xix, 16-19, 52
Time Stamps 54
TipWizard 74
title bar
Lotus 126-127
tool xviii
toolbar
auditing 175
top left corner 193
TOP-DOWN.WK4 247
top-to-bottom arrangement 10, 11-12, 14
totals
double-checking 163-165, 244
trace mode 129
tracers
Excel 173
transcript window 111-112
two-dimensional worksheets xix, 13-15
U
unprotected cells 26
upgrading a worksheet 166-167
use protection 194
user, communicating with 24
user inputs, verifying 23
user standards 5, 6
users' group
meetings 189
model modifications 189
software depository 188
synergy 189
using range names to navigate 15
utility programs 5
V
valid inputs 27
values
hardwiring xviii
values used in column D 194
variable assumptions 48-49
variables 144-145
Excel
declaring 145
VBA (Visual Basic for Applications) 137
verifying the environment 33
verifying user inputs 23
Version Manager
Lotus 220-224
index 223
reports 224
VERSION.WK4 247
VERSION.XLS 255
VERSION2.WK4 247
VERSION2.XLS 255
versions
Lotus 221-222
video projector 91
Visual Basic for Applications 137
VLOOKUP 233
W
warning messages 29-30, 41
warnings 39
group mode
Excel 19
Lotus 18
Watch Pane
Excel 154
Web xx
Word Basic 137
word processor, linking to 245
workbook objects
Excel 138
worksheet xviii, 159
auditing tools
Lotus 170-172
errors 160, 162
formatting
TipWizard 74
sheets xix
strategic purpose 1
worksheet arrangements 10-19
ad-hoc 11, 13
diagonal 14-15, 16
left-to-right 11, 12-13, 14, 16, 245
top-to-bottom 10, 11-12, 14
two-dimensional
ad-hoc 14
left-to-right 14
top-to-bottom 13
worksheet attributes
ease-of-use features 20
error-checking 20
external documentation 20
input prompting 20
internal documentation 20
worksheet consolidation 52
worksheet documentation 184
worksheet sections 9-10
arranging 10-19
ad-hoc 11, 13
left-to-right 11, 12-13
top-to-bottom 10, 11-12
history 10
input 9
macro 10
output 9-10
processing 9
worksheet standards 5, 6, 191
worksheet template 133-134
worksheets
2D 13-15
3D 52
audience, intended
frequent use by someone else 19
frequent use with modification by someone else 20
infrequent use with modification by someone else 19
auditing 169-175
auditing tools 169
error-resistant 162
formatting 65-66, 66-68
adding color 70-71
alignment of data 68
alignment options 69
borders 71-72, 240
column width 14
copying 72-73
fonts and numbers 67-68
Format Painter 72
informational screens 70
input section 70
rotated text 69
row height 14, 67
text wrap 69
using named styles 73-75
input checking 20
input section 23
bulletproofing 23
intended audience 19-20
frequent use by author 19
infrequent use by author 19
infrequent use by someone else 19
one-time use by author 19
macro section 180
review 161-162
rewriting 13
three-dimensional 16-19, 18, 52
two-dimensional xix, 13-15
top-to-bottom 14
upgrading 166-167
X
XLSTART.XLS 32
DESCRIPTION
This book teaches you how to develop large, bug-free, easy-to-understand, and easy-to-maintain spreadsheets. It explains how techniques that work on small spreadsheets fail on larger projects. Methods from this book can be critical when the developer of the spreadsheet isn't the one using or maintaining it. The Professional's Guide to Robust Spreadsheets is rich with real-life examples in both Lotus 1-2-3 and Microsoft Excel. This book helps you:
- Design for ease of use
- Plan for trouble-free maintenance
- Reduce programming errors
- Write spreadsheets that self-detect and correct wrong data
- Write effective reports
- Bring quality control to your design
- Set organizational standards for spreadsheet design
- Automate routine tasks with macros
WHAT THE EXPERTS SAY ABOUT THIS BOOK...
"I found myself marvelling at how relatively profound points could be
so clearly expressed. This refreshing clarity made the book fun as
well as educational."
--Allan G. Atherton, Computer Consultant
"This is the only book I've read that shows how to write better
spreadsheets, rather than simply use a spreadsheet program."
--Peter J. Luise, President, Renaissance Soluations
ABOUT THE AUTHOR...
Ronny Richardson, now a faculty member at Southern Polytechnic State University, was formerly a financial analyst at a Fortune 100 company where he developed and maintained thousands of spreadsheets, large and small. He has written 20 computer books, including several best sellers.