Data Linking in Excel by vth77339


Data Linking in Excel document sample

More Info
									Linking Excel Spreadsheets to Peachtree Data
                            by David Ringstrom, CPA
                                                                               Once you’ve selected the proper fields,
                            Although Peachtree® offers robust
                                                                            click on the Options tab; then click
                            reporting functionality, users are often
                                                                            Include Headings. If you’re sure that you
                            limited to the presentation of data, rather
                                                                            always want to overwrite any previous
                            than analysis of that data. Fortunately, it’s
                                                                            export file, select Overwrite Without
                            easy to export data to comma-separated
                                                                            Asking. Finally make note of the export
                            value (CSV) files, which Microsoft® Excel
                                                                            file name, which defaults to C:\ITEM.CSV.
                            handily imports. From there, your analysis
                                                                            Feel free to change the name and location
                            is limited only by your imagination. In
                                                                            of this file name, if you wish.
                            this article, we’ll demonstrate how you
                                                                               Now that you have the settings in place,
                            can build an Excel spreadsheet that you
                                                                            click the Save button to memorize this
                            can easily refresh with real-time data from
                                                                            export. Enter a name, such as Export
                            Peachtree, anytime you wish.
                                                                            Inventory Quantities, and click Save. The
                               Note that although it’s possible to
                                                                            default name of Inventory Item List is a
                            achieve the results of the functions
                                                                            reserved phrase in Peachtree, so you won’t
                            described in this article in Excel 97 or
                                                                            be able to use that name for your export.
                            earlier versions, it requires use of MS
                                                                            Now that you’ve saved these settings for
                            Query, which is beyond the scope of
                                                                            future use, click OK to create the export
                            this article.
                            Exporting data from Peachtree
                                                                               When you want to refresh your report
                            For our sample report, we’ll prepare a
                                                                            data, you can simply choose File, Select
                            simple inventory valuation report. The
                                                                            Import/Export and then click on
                            first step is to create an inventory export
                                                                            Inventory. After you select Export
                            format. In Peachtree, choose File, Select
                                                                            Inventory Quantities, click the Export
To check which              Import/Export, then Inventory. Click
                                                                            button; then click OK to refresh the
version of Peachtree        once on Inventory Item List; then click
                                                                            CSV file.
that you have,              Export. Click on the Fields tab and then
                                                                            Creating the Excel workbook
choose Help, About          click Show None (in earlier versions of
                                                                            You’re now ready to link this data to an
Peachtree                   Peachtree, click Deselect All). Now select
                                                                            Excel workbook. Although Excel could
Accounting. If your         the following fields by placing a check
                                                                            open the CSV file directly, you want to
Peachtree version is        mark in the Show column:
                                                                            establish a permanent link between the
8.00 or 8.01, you can          ❑ Item ID
                                                                            Excel workbook and the CSV file. To do
download the 8.02              ❑ Item Description
                                                                            so, create a new, blank Excel workbook.
patch from the                 ❑ Last Unit Cost
                                                                               The next step depends on your version
Peachtree web site             ❑ Quantity on Hand
                                                                            of Excel:
by choosing Online;         The Quantity on Hand field is the next-
                                                                                    Excel 2002 — Choose Data, Import
then Check for              to-last field in the list. This field is not
                                                                                    External Data, then Import Data.
Updates.                    available in Peachtree Release 7 or earlier.

10   ◆   Inside Peachtree
      Excel 2000 — Choose Data, Get                                                Figure 9. The Refresh Data on File
      External Data, then Import Text                                              Open option means Excel will
                                                                                   automatically retrieve the latest
                                                                                   version of your export file.
Next specify the file name that you
specified for the export file and click
Open. (If you didn’t modify the default
value in Peachtree, the file name is
   Excel then automatically starts the
Text Import Wizard to help you with this
process. Ensure that Delimited is selected
in the first screen of the wizard; then click
Next. During Step 2 of the wizard, select
the Comma check box and click Finish.
Choose New Worksheet and then click the
Properties button. As shown in figure 9,
deselect Prompt for File Name on Refresh,
since your file will have the same name
each time. Select Refresh Data on File
Open and click OK twice. You have now
linked this spreadsheet to the
C:\ITEM.CSV file. Based on the
instructions that you’ve provided to
Excel, it will automatically query the
C:\ITEM.CSV file when you open the
                                                Figure 10. Once you’ve completed the Text Import Wizard, your screen
                                                should look similar to this.
   The next step is to create an analysis
based on this data. Your worksheet should
look similar to figure 10.
   In general, when you’re exporting data       About the Author                                 Microsoft® Excel is a
from Peachtree to Excel, you’ll want to         David Ringstrom, CPA, helps Peachtree            trademark of
structure your workbook so that there’s a       users across the country leverage their          Microsoft
data page and a separate analysis page that     accounting software through the use of           Corporation.
contains your formulas. This enables you        Excel spreadsheets and Microsoft Access
to refresh your data without losing your        databases. To learn more, visit
formulas. By default, Peachtree sends data, e-mail
to the first page in the workbook, so we, or call
recommend that you create your formulas         404-252-3813.
on later pages.
                         Continued on page 12

                                                                                               December 2001      ◆     11
                            Continued from page 11
                                                                            Now choose five inventory ID’s and
                               On a blank worksheet within the Excel      descriptions of your choice from the
                            file containing your inventory data, create   inventory list; copy them from the
                            the spreadsheet shown in figure 11. Right-    Inventory worksheet and paste them to
                            click on the worksheet tab and choose         cells A2:B6 of the Report worksheet, as
                            Rename to name it Report. Right-click on      shown in figure 11. We’re now going to
                            the tab with your inventory data; then        use Excel’s SUMIF function to retrieve
                            rename it to Inventory. Type the headings     specific inventory figures from our
                            shown in row 1 of the worksheet shown in      Peachtree inventory data. The SUMIF
                            figure 11.                                    function has three arguments: where to
                                                                          look, what to look for, and what to add
                                                                          up. Thus, the formula in cell C2 in figure
                                                                          11 reads as shown in figure 12.
                                                                            In essence, this formula instructs Excel
                                                                          to look at all of column A of the Inventory
                                                                          worksheet for the inventory ID listed in
                                                                          cell A1. Each time that inventory ID is
                                                                          found in column A, add up the
                                                                          corresponding number in column D. In
                                                                          this case, since each inventory ID only
                                                                          appears once, we get the quantity on hand
                                                                          for that item. However, you can also use
                                                                          this technique to add up multiple rows,
                                                                          such as if you exported transactions from
Figure 11. Once complete, this spreadsheet will automatically update      Peachtree.
itself each time that you export your inventory data from Peachtree.

                            This article first appeared in the December 2001 issue of Inside Peachtree
                            and is reprinted by permission of Delicia, Inc., 3145 Reps Miller Road Suite B,
                            Norcross, GA 30071. For subscription information, please visit our website at
                   or call 770.840.8322

12   ◆   Inside Peachtree
   A benefit to SUMIF is that it will
automatically find your data, no matter
on which row the item appears. For               Figure 12. Type this formula in cell C2 of the Report worksheet.
instance, one month an inventory item
may be on row 10 of the export file, but if
you add new products, it could later shift
                                                  Figure 13. Type this formula in cell D2 of the Report worksheet.
to row 17. Regardless, SUMIF will always
retrieve the correct value.
   Now that you understand how SUMIF                                                Figure 14. Click the red exclamation
works, you can use the same approach to                                             mark to refresh the data in Excel.
retrieve the cost of the item. Cell D2 of
our example spreadsheet contains the
                                               Refresh Data icon on the External Data
formula shown in figure 13, because the
                                               toolbar, which appears automatically if
cost is shown in column C of the
                                               you click on a cell inside your inventory
Inventory sheet.
                                               list in Excel. This icon appears as a red
   Finally, cell E2 contains a simple
                                               exclamation mark on the toolbar, as
multiplication formula:
                                               shown in figure 14.
   Keep in mind that this is a simplified
                                               In this article, we created a simple
example to demonstrate exporting data
                                               inventory analysis spreadsheet. Once you
from Peachtree. Our report contains only
                                               establish the formulas, the report will
specific items that you select manually
                                               update itself automatically each time you
from the data page and copy to the report
                                               export from Peachtree. You can easily
page. Instead, you could copy all items
                                               build on this foundation to create just
from the data page to the report page to
                                               about any sort of analysis of your
perform an analysis on all inventory
                                               accounting records.
   With that said, you can now copy the
formulas in cells C2:E2 down through
C6:E6. Your inventory spreadsheet is now
complete. Next month, all you have to do
is export the inventory list from Peachtree,
                                                           Register to use the Subscribers Only
and then open this spreadsheet. Voila!
                                                           section at our web site:
Your report will be updated automatically.
   Alternatively, you can also refresh the
                                                           You can see the current issue
spreadsheet while it’s still open. Switch to
                                                           before it arrives in the mail, and you
Peachtree, follow the steps to export the
                                                           can search back issues on-line.
Inventory Quantities, and then click the

                                                                                                 December 2001       ◆   13

To top