Importing Data from Excel – Part 2 of 4 – Adding Data ...

News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating

May 2013

Importing Data from Excel ? Part 2 of 4 ? Adding Data Validation to Excel Worksheets......................................1

Control Freak ? Part III: Creating Controls within Accounts Payable...............................4

Estimating Tips and Tricks............................6

Online WebEx Training and Open Forums.....6

Adjust Column Feature..................................7

Are Your Assemblies Lean?.........................10

The Importance of Tracking and Managing Subcontractor Compliance..........................11

Importing Data from Excel ? Part 2 of 4 ? Adding Data Validation to Excel Worksheets

by: Michael Newland Event 1 Software

What do you do when you have a bunch of data in Excel that you need to enter into your accounting system? Do you enter it by hand? Hopefully not! Sage 300 Construction and Real Estate offers a variety of different import file formats allowing you to import things like journal entries, invoices, estimates, commitments, and more. So, the opportunity exists to take your Excel data, put it into the proper format, and then import it. That can save you a lot of time which is why this series of articles is devoted to teaching you how to leverage this capability.

In this four-part series, we're covering the following objectives:

1. Creating and Using Excel-Based Entry Grids ? Covered last time! 2. Adding Data Validation to Excel worksheets ? This Month! 3. Import-Enabling Existing Excel Worksheets 4. Importing Data from Excel with Parent/Child Records

In this part of the series, we'll address data validation in Excel. In reality, this topic is not confined only to the context of importing data because you can use data validation in a wide range of other applications in Excel.

In last month's article, we created a simple Excel-based data entry grid for General Ledger journal entries; so hopefully you still have that example handy! If you don't, don't feel bad, just download the example using the following URL:



The entry grid that we created last time is perfectly usable as-is except that it doesn't apply any rules that keep you from entering bad information (which might result in records being rejected by General Ledger).

In the scope of this article, we'll only use one simple example of validation, but keep in mind that the Data Validation feature of Excel could be applied to each column in your entry grid so that you can apply the appropriate rules for each of the values. In this example, we'll use Office Connector Query to provide a list of GL account numbers. We'll then use that list to validate

o Continues

May 2013

News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating

the data entry into the Account column of our worksheet. That way, it won't be possible for someone to enter an invalid account. To add the list of accounts, we'll use the Query Wizard which can be found on the Office Connector Query toolbar that should be displayed on the Add-Ins tab of Excel:

To follow the exercises, you'll need to have Office Connector Query installed with either an evaluation or full license of Office Connector Query Designer. If you need to obtain an evaluation license, just contact your Sage Business Partner or your Customer Account Manager at Sage. You can also watch a video of the exercises being performed:



Create a List

1. Navigate to Sheet2 of your workbook.

2. Click in cell A1 and type: Chart Of Accounts

3. Click in cell A3 and then click the

Query Wizard button on the

Office Connector toolbar.

4. When prompted, select your data folder and enter your login credentials for Sage 300

Construction and Real Estate.

5. On the first step of the wizard, select GL Account and click Next.

6. Check the box for the following columns and then click Next.

? Account

? Account Title

7. Click Finish since we're not applying any conditions or specifying any other options.

? A list of account numbers and titles should now be displayed in columns

A and B, starting on row 3.

Name the List

1. Select the range of cells containing the account numbers. This range starts with cell A4 (the first account number) and goes through the last cell in column A that contains an account number.

2. Click in the Name Box, type AccountList, and press Enter.

the TUG Pulse

Published by TUG,The Users Group for Sage 300 Construction and Real Estate & Sage Estimating,

and sent to all members.

Board of Directors Tim Cooke, President Scott Bishop, Vice President Sharon Hessong, Treasurer Jon Banse, Secretary Matt Weaver, Past President

Donald Bannister Alan Cusson Mary Jo Hamik Barbara Morse

Michael Suhovecky Marlene Williams

Lenni M. Witt

Publications Committee Natalie Allen Jon Banse

Sharon Hessong Tom Love

Liz Perez-Lavin Val Steffen Eire Stewart

Barbara Morse

TUG 3525 Piedmont Road Building Five, Suite 300 Atlanta, GA 30305

The Name Box is the box that sits above the upper-left corner of your worksheet. Normally, the cell address of your current selection is displayed here. You can type a name in this box in order to assign a name for a cell or group of cells. The name that you assign can then be used elsewhere as a cell reference.

? By naming this range of cells, it can now be referenced by Excel's Data Validation feature from anywhere in the workbook.

o Continues

u 2 u

e-mail: me@ Phone: 404.760.8171 Fax: 404.240.0998

Toll Free: 866.846.0999

e-mail me@

Website

News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating

May 2013

Apply Validation

1. Click back to Sheet1 and click inside the cell that is in the first data entry row of column A.

2. Click the Data tab on the ribbon and then click Data Validation. 3. In the Allow box, click the drop-down and pick List. 4. In the Source box, type: =AccountList

? Note that this is the name you assigned in the previous section to the range containing the account numbers

NOTE: Since the entry grid was created as an Excel Table, the data validation settings that we applied to the first row are automatically copied down to any new rows that are added as you enter data.

Notice that now there is a drop-down arrow in the data entry cells of column A. When clicked, you will see a list of account numbers to choose from:

Also, if you type an account number, it must be on the list or else you can't proceed. That's data validation at work! You can apply this concept to each of the other columns to control the range of acceptable values that can be entered.

Excel Data Validation

Below are some of the other uses and capabilities of Excel's Data Validation feature. Some of these features are explored in more detail in the online video.

5. Click OK to dismiss the Data Validation window.

? Limit the length of a text value. ? In cells that should contain dates, limit the input so that only

dates can be entered. Also control the range of dates that can be used. ? In cells that should contain numbers, limit the input so that only numeric values can be entered. Also control the range of values that can be used. ? Use custom validation by writing an Excel formula that returns TRUE (if the data is valid) and FALSE if it isn't. ? Control the messages that the user sees when the selection is in a given cell and when an invalid value is entered.

20

SAVE THE DATE

14

TUG National Users Conference May 6-9, 2014

Gaylord Texan Resort & Convention Center Grapevine, Texas

Plans are well underway to bring you a rip-roaring good time, so saddle up and join us for four days of non-stop educational sessions, hands-on labs,

roundtable discussions, third-party exhibits and lots of networking, not to mention the

down-home feel of Texas.

u 3 u

May 2013

News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating

Control Freak ? Part III: Creating Controls within Accounts Payable

by: Eire Stewart JP DiNapoli Companies, Inc.

In Parts I and II of this series, we looked at SAGE 300 CRE controls available within the General Ledger Module. In this installment, we will look at additional controls that are available at the module level. While this article explores controls available within Accounts Payable, similar controls are available within Job Cost, Accounts Receivable, Property Management, Service Management and other Sage 300 CRE modules!

As in General Ledger, many of the controls available in AP are found at the AP Settings level. These include controls that allow you to create Approval and Pending systems to limit when invoice batches can be posted, how transactions appear on your GL current ledger, and set-ups for Vendor Types which will prompt custom distribution grids in Invoice Entry. A limited number of controls are also available at the Vendor set-up level.

AP Settings

1. Located under [File], [Company Settings], [AP Settings]. The opening dialog box stores: a. [Pending system] ? can be set-up for a single or multiple users.

Can be used to control or limit when invoices are posted. b. [Use approval system] ? if ticked, Invoice Approval Settings

become available to the right. c. [Retain 1099 totals per GL prefix] -applies if you have multiple

Entities with separate Tax ID# in a single directory. Ticking this box will create the ability to issue Vendor 1099 by entity under the appropriate tax payer ID number.

d. [Include historical invoice file in verification] ? if you move invoices to history on a regular basis, then ticking this box will verify invoice numbers by vendor against those stored in History. It can eliminate duplicate payments on back dated invoices.

2. [Post & Interface Settings] ? controls the verification process and whether entries can be created in other applications. Unless you have a specific reason not to, tick them all!!!

3. [Invoice Settings] ? controls a number of the prompts that appear at data entry, most of which can be overridden in the entry process. Here you will find two boxes that determine what information is required at entry: [Date received usage] and [Payment date usage] ? if the required box is ticked then data will have to be entered at Invoice entry or the entry will not be allowed to proceed. a. [Distribution Settings]

? Sage 300 CRE has the ability to create Vendor types. The user names for these Vendor Types can be detailed under [Files], [Company Settings], [Custom Descriptions], [AP Vendor Type]. This grid is where the Vendor Types are assigned their distributions. As an example, a vendor Type of Job Cost would prompt for Job Cost Distributions. A Custom Description vendor would prompt a separate description line on each distribution (an example would be Federal Express where each distribution could be identified by the recipient). An equipment vendor could be created so that the distribution grid prompted for equipment information. We have a "Charge Back" vendor type that prompts for the information necessary to create a pass-thru invoice to Property Management. Vendor types are completely customized by the User. i. To designate a Vendor Type at the Vendor level, go to [Setup], [Vendor Setup] and select the appropriate type in the lower left hand corner of the [General] tab.

b. [Charge Back Settings] refer to Property Management and the ability to create Tenant Charges directly from AP Entry. These settings include how the invoice date is determined on the Tenant invoice, mark-up prompts and the Accounting Date to be used on the PM charge in GL.

c. [Warning Message Settings] ? these create some valuable controls by verifying key information stored in Job Cost and disallowing entry or creating operator warnings. i. The message choices are identical to GL Control Accounts. [Warning] creates a warning message but allows the operator to choose to continue with entry. [Error] creates a message and does not allow data entry to continue. [No Msg] allows entry to continue without notifying the operator but creates a message on the Journal.

o Continues

u 4 u

News and Information for Users of Sage 300 Construction and Real Estate & Sage Estimating

May 2013

ii. Tolerance amounts or quantities can be set for each option. Your options are: 1. [Total amount invoiced exceeds commitment] 2. [Total units invoiced exceeds commitment] 3. [Total amount invoiced exceeds estimate] 4. [Unapproved submittal for job] can be enabled or disabled. 4. Check Settings

b. The bottom portion of the window controls what information attaches to the GL transaction and can be seen on the Current Ledger and Account Inquiries, along with other reports. Multiple choices are available for all three fields. 6. [Payment Selection Settings] determines in what order invoices

appear during the selection process and what additional information can be seen in the selection window. 7. [Invoice Approval Settings] ? if [Use approval system] is ticked off, this window determines in what order invoices are displayed during the approval process and what additional information can be seen in the approval window. 8. [Orders] determines the order in which items appear in Invoice selection, Check printing and in Report/Inquiry tasks.

a. [Bank account use method] can be set so that the System automatically assigns per expense distribution ? this can be extremely handy for multiple entities. The account assignment can be overridden in the Selection process.

b. [Record Manual/Print Quick Checks] setting determines which updates the next check number stored in CM. Generally speaking, the correct selection should be [Quick check....].

c. Also found on this screen is [Lien waiver accumulation method]. Choices here will trigger the dollar amounts that are used to fill in the system generated Lien Releases.

i. A choice of [Accumulate by Job] will trigger a release for the total to date paid to that Vendor for a specific job. ii. [Accumulate by commitment and job, all costs] will produce a total that reflects all payments against commitments and also uncommitted costs that have been paid. iii. [Accumulate by commitment and job, commitment costs] will result in only those costs paid against commitments and will exclude uncommitted costs. 5. [GL Entry Settings] is where the designations are made for the

system generated side of data entry. a. These include designation of a [Credit Card Payable account] for use with the new Credit Card feature. Designating a specific Credit Card Payable account can help with reconciliation of credit card balances.

Vendor Set-up

[Setup],[Vendor Setup] also contains several control features: a. Terms and

Defaults: i. [Discount percent] and [Discount days] will automatically calculate the discount upon entry of each individual invoice. ii. [Payment days] and [Payment days type] will calculate the payment date based on the invoice date upon entry of each individual invoice. b. [Defaults] will prefill GL distribution, JC codes and an assortment of other information upon invoice entry. c. Both [Terms] and [Defaults] can be overridden in the invoice entry process d. [Check Settings] allows for each Vendor to have a setup when checks are created. An example is Utility Vendors where you may want [One invoice per check] to avoid checks being applied to the wrong account. e. Insurance 1 and Insurance 2 tabs control whether checks will be printed if insurance certificates have not been provided. Tick the Proof required box to prevent issuance of checks if Certificates have not been received or policies have expired.

Many of the decisions for these settings were made when Sage 300 CRE was originally installed. It might be worthwhile to take a second look and see if your original settings are still appropriate for how your company operates or if you want to take advantage of several of these control features to enhance your company's processing controls! Become a Control Freak!

u 5 u

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download