read this to count yourself up to date on submittal ...c.ymcdn.com/sites/ project. the fol-lowing...

June 2009

News andInformation for Users of Sage

Timberline Office Software

IN THIS ISSUEo Continued on page 4

The submittal process is an important part of as-suring construction qual-

ity. But keeping track of the large number of submissions can be a difficult task in any construction project. The fol-lowing numbering system and scheduling process may help you keep track of submittals while meeting common con-tract requirements.

The standard AIA Gen-eral Conditions require that the general contractor prepare a list of all submittals required

by: John WilliamsIllig Construction Company

for the project and the dates they are anticipated to meet the construction schedule. The submittal processor must review each specification sec-tion and identify each type of submittal required for the project. The processor must then determine which activity in the construction schedule is dependent on the timely processing of that submittal type and determine when the submittal must be received to assure an uninterrupted flow of material to the site.

A proper submittals list should identify the following:

The specification section.•The type of submittal.•The count for each type.•Revision number for •that submittal.

Many architects also re-quest a sequence number for the entire submittal list. They often refer to this as the Shop Drawing number (SD#).

The specification section should follow the project man-ual. The type of submittal can be identified by a numerical code, such as the following example:

Read This to Count Yourself Up to Date on Submittal Numbering and Scheduling .........................................1

11 Tips to Get Started: Stop Wasting $1,000s and Use Mobile Data for Timberline ....................................2

Reformatting Names in Cells ...............................................3A Checklist of New Skills Shows That Check For the

TUG Conference was Well Worth It ...............................8TUG Online Training

Schedule .................................... 10

The standard AIA General Conditions require that the general contractor prepare a list of all submittals required for the project and the dates they are anticipated to meet the construction schedule.

Read This to Count Yourself Up To Date onSubmittal Numbering and Scheduling

2 TUG Pulse June 2009

the TUG PulsePublished by the

Timberline Users Group, Inc.and sent to all members.

Board of DirectorsCharlie Auger, President

Robin Peterson, Vice President

Tom Moore, SecretaryNatalie Allen, Treasurer

Patrick HughesMatt WeaverEire StewartJulie Brown

Mike SuhoveckyDennis StejskalSharon HessongGary Simpson

Publications CommitteeEire Stewart

Robin PetersonJulie BrownDavid Wright

Tom LoveCarolyn Boettner, Editor

Maureen Connolly, Consultant

National AdministratorBoettner Business

ProfessionalsP.O. Box 697

Sylvania, OH 43560Phone: 419.885.9004

Fax: 419.885.9007

Toll Free: 800.884.4630

Web Site

o Continued on page 6

11 Tips to Get Started:Stop Wasting $1,000s and Use

Mobile Data for Timberlineby: Jeff Bonar

JumpStart Wireless™ Corporation

Mobile Data Entry for Timberline Will Save You $1,000s

In these tough economic times, your organization must operate as efficiently as possible. Sage Timber-line Office customers with field staff who don’t have access to a mobile application waste $1,000s getting paperwork—time cards, work orders, service orders, delivery tickets, esti-mates, authorizations, field reports, approvals, etc.—to and from the field. In traditional organizations, pa-perwork must be printed, distributed to the field staff, completed by them,

Manual paperwork is costing you $1,000s. Without mobile data, you must rely on wasteful manual pa-perwork to get information to and from the field. Once you have the paperwork in the office, you have to key data into Sage Timberline Office.

Mobile data delivers more reli-able payroll information (eliminat-ing “phantom time”), more timely billing, better control of job costs, and more satisfied customers.

Every major study shows mo-bile data saving between 20% and 30% of the cost to communicate with field staff. The big question: Is your organization ready?

In this article, I’ll give you 11 tips to successfully transition to mobile data. By using these ideas, you’ll have substantial returns on your investment.

returned to the office, checked, cor-rected, completed by the office staff, and finally entered into one or more Timberline modules. This process is slow, costly, and distracting—fraught with waste.

Not only does paperwork waste $$$$s, it costs in other ways. Infor-mation reaches the office hours or days late. Errors and omissions are common. Billing takes longer. All that tedious paperwork gets in the way of quality customer service. Instead of proactively managing their jobs, proj-ect managers have to put out fires. Office staff have to call service reps to give work order info, and service reps have to call the office to check in, ask for more work, obtain more details regarding assigned work, etc., etc.

Study after study has underlined the enormous benefits of mobile data delivery to and from your field staff. For example, Aberdeen Group’s study, Mobility in Today’s Service Organization, found a 19% increase in the average number of work or-ders per day, with a 13% increase in profitability. (See the report at http://www.aberdeen.com/summary/re-port/benchmark/5304-RA-mobility-service-management.asp.)

But Is My Organization Ready for Mobile Data?

Mobile software sounds great, but many businesses have concerns—all of which are valid and important. (The accompanying box lists some of them.) While this article can’t address

June 2009 TUG Pulse 3

A

1

2

3

4

5

BC

D


Excel Tips and Tricks

by: Coyle;James James CoyleEvent 1 Software, Inc.

Last Name; First Name. First Name Last Name. Sounds like one of my daughter’s Dr. Seuss

books. I’m thinking along the lines of One Fish Two Fish Red Fish Blue Fish.

Instead of letting you fish around for a means to control how names are displayed in Microsoft® Excel, I’ll make it easy and just tell you how to do it.

As Sage Timberline Office cus-tomers, we take for granted some of the useful tools built into Timberline that aren’t automatically pres-ent when using other technology. An example of this is the ability, when creating vendors, employees, etc., to type in a name in the Last Name;First Name format and then be able to see that name (and to sort names) by last name, or to switch the way that name is displayed to First Name Last Name.

But if this information is placed into Excel, Excel doesn’t inherently know what to do with it. To Excel, the Last Name;First Name information, regardless of the format or syntax, is just a bunch of text.

My co-founding partner here at Event 1 Software, Inc. provided the following information to one of our

customers recently, and we felt the information would be useful to any Excel user—those using Office Con-nector Excel integration or not.

The information below explains the thought process for switching the first name and last name values when names are entered as “Last Name”; “First Name”.

Although each step is sepa-rated into separate formulas in the columns below, these could be combined by nesting the respective formulas into a single expression. That may be desirable in some situ-ations, but would certainly result in a much more complex expression.

Semicolon LocationThe key is to first identify the

location of the semicolon charac-ter. That can be done using Excel’s SEARCH function. SEARCH(“;”,A2) would return a number that indi-cates the character position of the semi-colon within the text in Cell A2. A nuance of this function is that if the search text isn’t found, then it returns #VALUE. That makes the result useless for including in other functions unless you do something else with it. In the above example, that’s why I added a semicolon to

the end so that even if the name in Timberline doesn’t include a semi-colon, one is still found at the end. That causes the other formulas to consider the entire text value to be the last name, and the first name would be nothing.

Last NameThe formula in Column C simply

makes use of the semicolon location using LEFT function to parse out the first N number of characters that represents the last name. Note that I used the semicolon location minus one so that the semicolon is not included as part of the last name.

As Sage Timberline Office customers, we take for granted some of the useful tools built into Timberline that aren’t automatically present when using other technology.

Reformatting Names in CellsReformatting Names in Cells


Read This to Count Yourself Up to Date on Submittal Numbering

01 Product Data, Cut Sheets, Manufac-turer’s Certification or Approval Letters

02 Shop Drawings 03 Samples04 Mock-Ups05 Certifications06 Qualifications07 Test Reports08 Calculations 09 Pre-Installation Meetings10 Close-Out Documents11 As-Built Drawings, Re-

cord Documents12 Warranties

The count for each submittal type can vary based on the com-plexity of the item. You may receive separate shop drawing submittals for anchor bolts, beams and col-umns, and stairs—all under the structural steel specification. Each of these separate shop drawing submittals would receive a separate count number.

A decimal separates the submit-tal count from the revision number. The initial submission would be 0. If rejected, the first revision would be 1. The second revision would be 2, and so forth.

We’ve created a custom field in PJ for the Submittal Sequence Number. This number represents the order of submission of all submit-tals. This field is not automated and must be entered manually. We’ve

o Continued from page 1

Figure 1

| Number | Rev. SD# Description 03200-02-1.0 3 Reinforcing Steel Shop Drawings (Foundations)

SD # Third submittal overall

Review cycle / Revision number Corresponds to the submission cycle, this is the initial submittal

Count First Product Data submittal

Submittal Type 01 - Product Data submittal

CSI number Structural Steel specification section

Figure 2

Submittal Type may also be identified by this drop-down field. You may customize the field to match your code by selecting PJ Sbm Types in Custom Descriptions.

CSI Number

Submittal Count

Submittal Type

Revision Number

Figure 3

Overall Submittal Count



created a Submittals Log report sorted by SD# in descending or-der that we can pre-view on the screen to determine where the submittal sequence number left off. You may wish to use PJ’s Number field as your sequence number and create a custom field for the submittal type and count.

Structure of the Numbering System

The entire number-ing system is structured as follows in Figure 1.

The introduction of this numbering system into Sage Timberline Office Project Manage-ment would look like Figures 2 & 3.

The Look of the Initial ReportOur report com-

bines the CSI number, Type, Count, and Revi-sion numbers from the header section of the Submittal window and is then followed by the Submittal Sequence number from the Cus-tom Fields window. The initial report appears as in Figure 4.

This numbering system may allow you to filter out those sub-

mittals that are not required until the end of the project, such as close-out items. It may not be necessary to report these items repeatedly on a weekly submittal review. If you receive more submittal items than you antici-pated, you may eas-ily append the list by adding to the submittal count.

Since no submittals have been received, the Sequence Number (SD#) is blank. The SD# will be introduced in the order in which the submittals are actu-ally received. To satisfy those architects who want to see the list in the order in which sub-mittals were received, you may sort the report in descending order by SD#.

Now on to DatesOnce the list of an-

ticipated submittals is prepared, the next order of business is to prepare the date each submittal is required from your subcontractors and the required return date of that submittal from the reviewer. To compute these dates, you need

Figure 4

Figure 5

Figure 6

Required Dates


Stop Wasting $1,000s and Use Mobile Data for Timberlineo Continued from page 2

every issue in detail, the following will start you in the right direction toward a workable, effective, and cost-saving mobile solution.

Eleven Tips for Making Mobile Data Decisions

The fundamental factor for con-venient and profitable mobile data is making the process easy for your staff. These 11 tips will help you decide what you need and how to implement mobile data for your company:

1. Work with a mobile data vendor who can put the pieces together for you.

For many businesses, “mobile” seems like the Wild West: different devices, wireless carriers, and soft-ware technologies, each with its own technical peculiarities. How can they be made into one complete solution? Many companies are willing to put all these pieces together for you—you don’t have to figure out the complexi-ties on your own.

But beware of vendors looking to sell you just a device, just the soft-ware, just integration with Timberline, or just the monthly data plan. Look for a vendor with a complete solution ready to roll out to your staff. Make sure the application can meet your long-term needs, i.e., mobile print-ing, bar code scanning, and location management. Ensure that the vendor is a Certified Sage Business Partner. Certified companies understand the complexities involved in effective Timberline integration.

2. Mobile data should be delivered directly into Timberline.

Sage Timberline Office is great management software. With Timber-line, TUG members have taken their

businesses to the next level. There’s no reason to consider a mobile solu-tion that requires you to change the Sage Timberline Office modules and processing that you are already us-ing. Mobile software that does not integrate with Timberline will most likely deliver little or no value. De-pending on your needs, integration may involve multiple Sage Timberline Office modules, i.e., Payroll, Service Management, and Job Cost.

3. Leverage your existing paper forms and processes.

You’ve already done extensive work building processes and sys-tems to best manage your business. Those existing processes are built into the paper forms your field and office staff currently use. Work with vendors who begin developing your mobile data system with your paper forms. Your staff will learn much faster if the mobile screens look like the paper forms they’re familiar with. Why adapt your company to someone else’s approach?

4. E-mail is not the complete solution. Implementing a mobile application that replaces existing “paperwork” (work orders, inspec-tion reports, payroll timesheets, delivery orders, etc.) is still the best way to visualize and imple-ment mobile software.

E-mail isn’t the replacement for paperwork. E-mail cannot capture or manage the detailed data fields necessary for Timberline databases. Mobile field work is task-focused and step-by-step oriented. The mobile application you choose needs to reflect how your employees actually do their jobs. Look for software that allows you to present step-by-step processes that optimize field work-ers’ time and effectiveness. Familiar step-by-step processes make the

transition to and implementation of your mobile system easier, and therefore, save you $$$$.

Business Concerns:Can I really save $1,000s per •month?Business is down; can I af-•ford the capital investment?Will mobile software deliver •on its promise AND work well with Timberline?My business is special, my •processes are special, and my customers are special. Will mobile software allow me to keep doing business my way?

People Concerns:I don’t know if my staff can •effectively use this technol-ogy.My field staff doesn’t like the •idea of the office watching their every move.We’re not really computer •types.

Technology Concerns:Are the devices real ly •ready?How well does everything •work together?We have laptops in the trucks, •but people don’t like to use them.Does this require rugged de-•vices that cost $1,000s? My staff need to be in places •that have poor or no cell phone coverage.We have e-mail. Isn’t that •enough?


5. Cost justification; Return on Investment (ROI).

Start analyzing your return on investment (ROI) by understanding ALL the costs of your manual paper processes: in the field, in the office, in extra driving time, etc.

After picking a potential mobile approach, determine the initial and daily expense (software, devices, initial set-up fees, monthly service fees, and wireless carrier costs). Then determine the daily savings by using mobile technology. How fast is your payback? What is the monthly savings once you’ve paid for the solution?

To see an example of return on investment analysis, an example that walks you through all the details, go to www.jumpstartwireless.com/roi. This ROI tool focuses on service busi-nesses, but it gives you an example to adapt to your particular business.

Studies have shown that because of the increased productivity provided by mobile applications, each field employee has an additional 1–2 hours a day to do revenue-generating work. Mobile delivery of time cards eliminates errors in employee cal-culation—saving payroll “phantom time”—averaging 50 minutes per week per employee in the field. (This number is from the American Payroll Association.)

6. Wireless applications should work with any device and with any wireless carrier.

The world of wireless devices and applications is undergoing rapid, intense development. Today’s cutting edge, breakthrough products are ob-solete in 6–9 months and then are no longer available for sale. Screen sizes, keyboards, printers, bar code scan-ners, and cameras improve at a rate of 10 times with each new release. Along with the innovation in devices,

carriers announce ever-faster and cheaper wireless data services. Sage Timberline Office customers cannot afford to be dependent on such rap-idly changing technology. You need a solution that will continue to work through the evolution of devices and networks—without reprogramming. Discuss with your mobile data vendor how your investments in wireless software design, training, and deploy-ment will last 3–5 years.

7. Mobile software should not require expensive custom pro-gramming and time-consuming systems integration.

Just as you bought Sage Timber-line Office instead of programming your own accounting system, you don’t want to put together your mobile solution yourself. Look for a vendor with a solution that’s tailored to your operations and ready to roll out with minimal expense and effort.

8. Look for familiar, cost effective, and mass-market devices.

You can buy $1,500 specialized personal devices, ruggedized and designed for the nastiest field condi-tions. You can also use a BlackBerry (about $100) or, in many cases, a free cell phone. These mass-market de-vices are familiar, easy to use, and are replaceable if damaged. As long as your mobile software gets your data to a safe place as quickly as possible, you’ll find far more value and ease of use in the off-the-shelf phone.

9. Practical mobile software must continue to operate, even when the device is out of coverage or disconnected from the network.

Mobile devices are commonly out of coverage as field workers go in and out of building cores, in and out of remote parts of town, and in and out of coverage areas. Your business

demands that mobile software oper-ate “non-stop”: a user must still be able to work, even if the connection to the wireless network is lost. When back in coverage, all data should be transparently and reliably delivered to Sage Timberline Office.

10. Identify one task where you know your processes are inef-ficient.

Don’t try to address every prob-lem or every business situation immediately with a mobile solution. Reach for the “low-hanging fruit”—the simplest business task you can improve by using mobile technol-ogy. Choose something that has an obvious, immediate payoff. Save the complexities and special cases for later. Once one task is mobilized, the rest of your business processes will easily follow.

11. Don’t wait.Time is $$$$. By making one

task mobile, you’ll learn more about mobile devices and software than you would with a year of study. Start today by choosing a competent, experienced vendor who will under-stand all of your unique needs. Mobile solutions have a payoff that you can’t afford to miss.

At JumpStart Wireless™ we are experts in providing affordable, complete, easy-to-use wireless solu-tions tailored to your specific needs. Contact us to find out more about gaining the benefits of mobile data for your organization: 888-233-8132 or [email protected]

Copyright © 2009 JumpStart Wireless Corporation, All Rights Reserved.


Whenever I return from a TUG National Conference, I try to create a “to do list” so my boss is confident he got his money’s worth by sending me. And because I’m accountable to him to implement what I’ve learned, I use the following type of list to detail my many new skills and to record when I put them into action:

A Checklist of New Skills Shows That Check For the TUG

Conference was Well Worth Itby: Maeva Mayes

Dean Crowder Construction, Inc.

# Module Description Done Date

1 Payroll Set up an Active button under the Misc Info tab in Em-ployee Setup. Set up a condition on PR reports to sort on the Active button.

2 Job Cost Set up an Active button under the Status tab in Job Setup. Set up a condition on JC reports to sort on the Active button.

3 Security Set up security so that Address Book information is blocked from most users. Only one person should enter the company and contact information.

4 Address Book Set up our customers’ company and contact information.

5 Backup Set up a saved Backup queue. Be sure to include custom reports, formulas, templates, and PVData in the backup.

6 Print Driver Make sure that HP driver PCL5e is set up for all comput-ers printing from Timberline.

7 Cash Management Use ADJ in Cash Management instead of DEP for Inter-est Earned. Timberline tracks the year-to-date interest income.

8 Cash Management Change the CM Custom Description drop down menus to reflect the items that we use most.

9 File Maintenance When moving data to History, always do this function at the server, and make sure no one is in Timberline.

10 File Maintenance Named Files: When moving data to History, use named files so that users won’t get confused and work in ar-chived data.

11 Security Set up security on data folders so that users won’t get confused and work in archived data.


We all learn much at the TUG Conference. This is a great way to show just how much we learn.

12 Year End Close Check-lists

Update the year-end checklists for our company (with thanks to the TUG Conference session, Year-end Proce-dures and File Maintenance, led by Lennie Witt).

13 Job Cost Use Miscellaneous Worksheets to enter Estimated Pro-duction Units for Jobs in ESTPROD.

14 Macros Set up macros to post in all modules before starting to reconcile the month end.

15 Macros Set up macros to run month-end reports.

16 Report Designer Use colored text and/or drop downs to alert the end user where special attention is needed (i.e., call attention to prompt fields where job number may/may not require punctuation).

17 Job Cost Use Msc Job Amt 1 to track prior year profit by changing the description in Custom Descriptions.

18 Job Cost Enter prior year profit on CONTRACT view; select the JOB button and a box will pop up for entering Prior Yr Profit.

19 Job Cost Set up a Subcontract Compliance Checklist. Go to Custom Descriptions and change the JC Misc Checklist 1 through 4 to whatever fields needed.

20 Notes Use notes throughout the software to record additional information needed for internal purposes; add this field to reports.

21 Accounts Payable Lien Waivers: Modify the Lien Waiver Report design to reflect our company’s needs. Activate the function so that we can prepare lien waivers with all subcontract pay-ments.

22 Report Designer JOIN: Set up the JOIN formula in all modules that have address fields and save them as public formulas for joining addresses, states, and ZIP codes for customers, employees, contacts, companies, and vendors.

23 Project Management Set up one view for Change Request data entry. Go into Custom Descriptions and change PR-Price to our com-pany’s initials. PR-Price is in the list twice, so make the change twice. Next go into Project Management, Contact Control, Change Request, then go to the tab Price/Cost Estimate. The default view should be the one called our company’s initials. Right click on the heading and choose “hide/show columns”. Choose Cost Code, Description, Labor Price, Labor Cost, Material Price, Material Cost, Subcontract Price, Subcontract Cost, Vendor, Vendor Price, Other Price, and Other Cost.

**** The key is to make sure when entering a change request that you enter the cost code then slide over to the Vendor field and enter it and the vendor price—then hit Enter. Move the cursor back up the same line and fill in the remaining information.

# Module Description Done Date


All training sessions take place at 1:00 EST (unless otherwise stated) and last approximately

one hour. To register, email [email protected]. Just let her know which class(es) you would like to participate in. We’ll get you registered and email a confirmation along with connection information. These classes are free of charge and open to members only - one more benefit of belonging to the Timberline Users Group !

We are adding more sessions every day. Check the Training Calendar at www.TUGweb.com for an updated list. If you have any suggestions on sessions you would like to see, please email [email protected].

TUG ONLINE WEBEXTRAINING & OPEN FORUMS

TUG ONLINE WEBEXTRAINING & OPEN FORUMS

DATE TOPIC

May 18-21, 20102010 TUG National Users Conference & Workshops

Orlando, Florida

April 26 - 29, 20112011 TUG National Users Conference & Workshops

Grapevine, Texas

May 15-18, 20122012 TUG National Users Conference & Workshops

Orlando, Florida

Mon 6/1/2009 Timberline Security 9.5/9.6

Tues 6/2/2009 PJ: Change Mgmt Workflow & Change Requests

Thur 6/4/2009 Beginning Payroll Setup

Mon 6/8/2009 Managing Timberline Address Book

Mon 6/8/2009 Advanced ODBC for Financials

Tues 6/9/2009 PJ: Change Request Crystal Reports Modifications

Thur 6/11/2009 Beginning Payroll Workflow

Mon 6/15/2009 Setting Up Timberline Desktop

Tues 6/16/2009 PJ: Owner Change Orders & Reports

Tues 6/16/2009 SERVICE MANAGEMENT OPEN FORUM

Thur 6/18/2009 CONSTRUCTION OPEN FORUM

Thur 6/18/2009 REAL ESTATE OPEN FORUM – 2:00 EST

Mon 6/22/2009 Desktop Tools: Log Viewer, Workflow Centers, Job Central, Reconciliation Tool

Tues 6/23/2009 PJ: Commitment Change Orders & Reports

Wed 6/24/2009 ESTIMATING OPEN FORUM – 11:00 EST

Thur 6/25/2009 Payroll Deductions, Pay ID’s and Pay Groups

Mon 6/29/2009 IT OPEN FORUM

Tues 6/30/2009 PJ OPEN FORUM

Thur 7/2/2009 Reconciliation – JC, AP, PR, AR to GL

Mon 7/6/2009 Crystal Reports 11 Tips and Tricks

Tues 7/7/2009 PJ: Settings, Custom Descriptions, Custom Fields & Defaults

Thur 7/9/2009 Beginning PO/IV

Mon 7/13/2009 Setting Up My Assistant

Tues 7/14/2009 PJ: Using Address Book with PJ

Thur 7/16/2009 CONSTRUCTION OPEN FORUM

Thurs 7/16/2009 REAL ESTATE OPEN FORUM- 2:00 EST

Fri 7/17/2009 Estimating Tips & Tricks

Mon 7/20/2009 Office Connector


Tues 7/21/2009 PJ: Job Setup and Workflow


Thur 7/23/2009 Entering, Editing and Importing Budgets


Tue 7/28/2009 PJ OPEN FORUM

Thur 7/30/2009 Equipment Costs

Mon 8/3/2009 Beginning ODBC

Tue 8/4/2009 PJ: Daily Field Reports

Mon 8/10/2009 Intermediate ODBC

Mon 8/17/2009 Advanced ODBC



Thurs.8/20/2009 REAL ESTATE OPEN FORUM -2:00 EST

Thurs.8/20/2009 CONSTRUCTION OPEN FORUM


Tue 8/25/2009 PJ OPEN FORUM

Thurs.8/27/2009 AP Checks, Selecting, Voiding, Modifying Formats


Read This to Count Yourself Up To Date on Submittal Number-ing and Schedulingo Continued from page 5

to anticipate delivery time, fabrica-tion, release time from contractor to fabrication, and the review period. We’ve created a Microsoft® Excel template to assist in back-calcu-lating the start, finish and on-site required dates. (Figure 5)

The back-calculated dates are then introduced to PJ. (Figure 6)

The initial submittal report with the required dates would then ap-pear as in Figure 7.

With the anticipated list com-plete, with the required dates, you may use the dunning request letter to solicit submittals from subcon-tractors. As the individual submit-tals are received, the SD# is added, and the actual dates and status are updated. A completed submittals re-port would look as follows in Figure 8. (Note the revisions to submittals in irrigation and reinforcing steel.)

To complete our process, we stamp each submittal with the submittal and sequence numbers. We then review submittals for con-formity project requirements before forwarding them to the reviewer. Occasionally we reject submittals for non-compliance and send them back to the subcontractor, skipping the architect.

Attempts to automate this pro-cess through links with Microsoft® Access have been inconsistent and unreliable. The steps necessary to acquire activity information from our scheduling program electronically, conduct the back-calculation and

then transfer the required dates to PJ have proved to be too compli-cated for our users. They’ve opted to enter submittals and required dates manually.

Our previous project manage-ment software allowed us to im-port our entire submittal list from a .csv file. It also linked directly to our scheduling software and performed the back-calculations within the submittal window. Help-ful PJ product enhancements would be a simple import tool and back-date calculator, as well as a user-

Figure 7

Figure 8

definable field for submittal type and count.

I hope this numbering system and calculating tool help you when processing submittals. Please share your comments, questions or sug-gestions. Go to TUGweb.com with your ideas or send them to me at [email protected].

TIMBERLINE USERS GROUP, INC.POBox697•Sylvania,Ohio43560

PRSRT STDU.S. Postage

PAIDRockford, IL

Permit No. 745

A

1

2

3

4

5

B C D E

I wrapped this in a TRIM function because if there is no first name, I wouldn’t want the result to start with a space.

All of these functions could be nested together into one large for-mula, if desired. However, breaking it up like this makes it easier to understand and to edit.

Excel Tips & Tricks:Reformatting Names in Cells

First NameThe formula in Column D makes

use of the MID function which is a function that pulls some number of characters out of the middle of a text value, starting at some location (similar to Timberline’s PART func-tion). The first argument of the MID function is the text to be parsed. The second argument is the start-ing location, and so I’m using the location of the semicolon plus one (again, so that the semicolon is not included as part of the first name). The third argument is the number of characters to read. This is a required

o Continued from page 3

argument, and so I set it to a suffi-ciently large enough value to get the remaining amount of text. Finally, I wrap the MID function inside a TRIM function. The TRIM function removes any leading or trailing spaces. I’m mostly concerned with leading spaces since a name could be en-tered as “last;first” or “last; first” (with a space after the semicolon). The TRIM function would eliminate the space.

Name SwitchedThe formula in Column E simply

joins together the first name plus a space, plus the last name. Again,