MARCH 2020 PCWorld 125F4: =SUM(E410%), the current
discount percentage in your store, then
copy from F4 down to F5:E500
G4: =SUM(E4-F4)6.25, where 6.25 is
the sales tax in your area, then copy from G4
down to G5:G500
H4: =SUM(E4-F4+G4), then copy from
H4 down to H5:E500
Now that you have all the spreadsheet
formulas in place, all you have to do is enter
the quantity (column C) for each computer
sold (daily, weekly, or monthly). If the prices
change, enter the new prices in column D.
The rest of this database is all formulas or
static information.
- As seen above, with “macro”
spreadsheets, you set the formula range to
be many rows beyond the last record, so you
can just add new records at
the end and not worry about
adjusting the range. Because
the macro sorts the
database, the new records
are relocated to the proper
position. The spreadsheet
data in our example ends on
row 210. The formula range
extends out to row 500, so
it’s safe to add the next new
record on row 211. - Once the
spreadsheet is defined and
set up with the structure,
static data in place, and
correct formulas, make 12 copies in
worksheets 2 through 13. Edit the tabs on the
bottom to identify the individual stores.
Change the name of the sheet1 tab to Master,
because this is your master database file.- Change the location data on row
1 to identify the store information (that
matches the store on the tab) on all 12
spreadsheets. Next, email an electronic
copy of each branches’ spreadsheet to
each of the store managers; for example,
send the Boston sheet to Boston, the
Dallas sheet to Dallas, etc.
Their copies include the spreadsheet
formulas that work on their individual
spreadsheets (but not the formulas of the
combined spreadsheets in the workbook). - The macro provides the formulas for
Copy the Master spreadsheet 12 times, then name the tabs.