MARCH 2020 PCWorld 123
- Enter this date formula in cell A1:
=Today(). Now this cell always displays
today’s date. Be sure; however, that your
store location (branch name and number)
are entered in B1.
- Leave row 2 blank. Once the static
data and initial dynamic data are entered,
we’ll use row 2 for the totals. This might
seem like a strange custom, but for macro
spreadsheets, it’s the best way because this
row is stationary and always visible.
- Next, enter the field names (and/or
any other field-specific information) in row 3
(e.g., from A3 through J3, or however many
fields your spreadsheet requires).
Tip: You can text-wrap the information in
the individual cells if the data is lengthy. For
example, you can put the store contact
information all in one cell and wrap the lines.
Press Alt+ Enter to insert
extra lines in the cells.
- Next, enter the static
data in column A. That is the
record information in your
spreadsheet that rarely
changes. If your business uses
product numbers or ID
codes, which are unique
because there is only one
code per product, enter
those in column A beginning
on row 4 (don’t skip to row
5). Other static data fields
might include the Product
Description, the Product Price, sales tax
percentage, etc.
Do not skip rows or leave any rows blank
for column A. Every row must contain the
unique field’s data—if not a product code,
then some other unique identifier. We do
this for two reasons:
- Column A is the main navigational
column. The macro moves and navigates
through the spreadsheet based on the
Home (A1) position and column A. The
macro will fail if you ignore this rule, because
blank rows disrupt the actions of the
directional keys.
- If you decide to create multiple/
relational tables later for Pivot Reports, you
must have a unique, key field to connect the
related tables. Check out our Excel pivot
tables tutorial for more information (go.
Build the Master spreadsheet first.
