Did you know that you can export to Excel from look-up screens and any Grid in Sage 100? Here is a Bank Reconciliation example of exporting from a Grid:
Bank Reconciliation > Main > Check, Deposit and Adjustment Entry or Reconcile Bank – Just right click anywhere on the Grid then choose Export to Excel at the bottom of the menu.
Think of the places where this could be used: GL General Journal Entry, AR Invoice and Cash Receipts Entry, Payroll Data Entry, Sales Order and Invoice Entry, Purchase Order Entry…. and the list goes on.
What about Masterfile Lookups that can be customized to show any fields you want to see and then export to Excel? An example of that might be a list of your 1099 Vendors to make sure you’ve filled out the Company Tax ID behind the Vendor. In addition, you’d like to see the last time you paid the Vendor, so you don’t have to bother with Vendors who didn’t have any activity in the current year.
Here’s how to set up a Customized Lookup Screen – in this case, for the 1099 Vendors. Navigate to Accounts Payable > Main > Vendor Maintenance. Click the Custom button in the lower left corner, choose ‘Create new view’, and give it a Description (1099 Vendors for our example here). Don’t check the Default View button unless you always want this Lookup Screen to be the “Default Screen” when you use the magnifying glass or F2 Key in the future to see a list of your Vendors. Click Next.
The list of columns on the right is what you are going to display. Available fields are on the left. You can remove the Zip Code and Sort field, but keep the Phone Number as you might need that for the 1099s. Then add the following: Vendor Type, Form 1099, Taxpayer ID/Social Security No, Box 1099 and Last Payment Date.
Click Next. Now you’ll enter the Filters. We’ll choose Vendor Type Not Equal To N, which means we’ll get all of the Businesses and Individuals that are flagged for 1099s. We also want only Active Vendors, so we’ll add the Status to be Equal to “Active”. This is what your screen will look like:
Click Next. Now we’re in the final step. We can name the Lookup and choose the Default Search. Here is what I recommend: Search by Name, Contains
Click Finish and you’re ready to test it out. Your screen will return to the Default Lookup.
Click on the “View” Dropdown (that shows “Standard” above) and you’ll see a new 1099 Vendors option. Select that option, and VOILA! The 1099 vendors will be selected.
Now you’re ready to export to Excel. At the bottom of the screen, in the lower right hand corner, you’ll see an Excel icon which will automatically export to Excel:
Here’s what it looks like in Excel with one easy place to see all of your 1099 Vendors, with the type of Vendor (Individual or Business), which 1099 Form the Vendor is set up with, and which Box is the Default 1099 Box.
Exporting information from Sage 100 is easy. Hopefully this will help give you some ideas of where you might use the Export to Excel function, and as always, if you have questions, please don’t hesitate to give us a call!