home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Media Share 9
/
MEDIASHARE_09.ISO
/
utility
/
pbaseiv.zip
/
P4SPR003.TIP
< prev
next >
Wrap
Text File
|
1991-12-16
|
3KB
|
80 lines
I've discovered a way for one Quattro Pro worksheet to look
up data in another. For instance, if you keep a table of
sales tax rates in one file where it can be easily updated,
you can use that data to calculate the sales total in
another. This same technique can be used for other types of
information, such as minimum order or discount levels; the
effect is very much like that of a relational data base.
The worksheets SALES.WQ1 and TAXRATES.WQ1 [illustrated below
and included in the P4SPREAD directory on your PowerBase
*.* Volume IV disk] show how this works. To try them out,
copy both to your hard disk, make their directory the
current one, and load SALES.WQ1. Then, adjust the paths in
cells A1 and B2 to specify the complete pathname of
TAXRATES.WQ1 (e.g. C:\WORK\TAXRATES.WQ1). When you enter a
state abbreviation in cell B1 of SALES (you can use capitals
or lowercase letters) and a dollar amount in cell B3, you'll
get a sensible label in A2, plus the calculated state tax
and total in cells B4 and B6.
The secret to this trick is the formula
`@@("[taxrates.wq1]"&B1)'
in cell B2 of SALES.WQ1. This dynamic link fetches a value
from the cell in TAXRATES.WQ1 whose range name matches the
string in B1. Since the state abbreviations (in column A of
TAXRATES.WQ1) are range names for the tax rates (in column
B), this amounts to looking up the tax rate in a table.
This technique has one slight quirk: Quattro Pro does not
recognize this kind of formula as a link between worksheets.
Therefore it will not remind you to load the supporting file
(TAXRATES, in this example) when you load the main
worksheet, and the supporting file must be open in order for
the link to work. The solution is to put a more
"conventional" link in the main worksheet, such as the
formula `+[taxrates.wq1]A1' that is shown in cell A1.
David H. Smiley
Virginia Beach, Virginia
Editor's note: If all your potentially related files are in
the same directory, you don't need to include full paths in
your linking formulae. Including the path, however, will
give you the freedom to keep those files in separate
directories and access them regardless of the current
directory. Finallly, please note that the sample worksheets
oversimplify the task of sales and use tax calculation. Many
states have hundreds of different jurisdictions with
different tax rates.
TAXRATES.WQ1, a sample Quattro Pro sales tax worksheet, is
used as a lookup table by SALES.WQ1.
A B
1 State Sales Tax
2 NC 5.0%
3 PA 6.0%
4 VA 4.5%
SALES.WQ1, a sample Quattro Pro worksheet, uses state sales
tax information from TAXRATES.WQ1 to calculate the full cost
of a sale.
A B
1 +[taxrates]A1
2 @UPPER(B1)&" tax rate" @@("[taxrates.wq1]"&B1)
3 Sale
4 State tax +B3*B2
5 \-
6 Total +B3+B4
Title: Looking Up Quattro Pro
Category: SPR
Issue date: Jun 1991
Editor: Karl Koessel
Supplementary files: P4SPREAD\TAXRATES.WQ1,
P4SPREAD\SALES.WQ1