Embedded Files
Product: QuickBooks Premier - Accountant Edition
0. Backup
Before performing any updates the first step is to perform a backup of the existing database.
Select File.
Select Create Backup...
Select Create Local Backup.
Click Finish.
1.Update Price
Exporting Item list to Excel:
Select File.
Select Utilities.
Select Export.
Select Lists to IIF Files.
When the Export window appears, choose the list type you wish to affect. For purposes of this article I’ll check the Item List If you choose more than one list type, the lists will be stacked into a single IIF file, so I recommend only exporting one list type at a time.
Click OK to export the list to an IIF file.
When prompted, specify a name and location for the file, such as IIF on your Desktop.
Click Save to export the list to an IIF file.
Importing IIF File into Excel:
Within the Open window in Excel change the File Type filter to All Files and then double-click on your IIF file
Click Finish when the Text to Columns window appears. In this context all of the default settings match our requirements.
At this point you’ll want to arrange your worksheet so that you can freeze the worksheet panes:
Scroll the worksheet so that the first cell containing !INVITEM appears in the upper left-hand corner of your screen.
Select Column C or cell C21.
Select View, Freeze Panes, and then Freeze Panes.
At this point when you scroll to the right you should always see columns A and B on the screen. As you scroll to the right you’ll see column M has a caption of PRICE. This is the field that contains the price for an item.
Create additional columns to help you to transfer pricing to IIF file.
Open the spreadsheet containing updated pricing. It will contain three columns: Name, Description and Price. Sort all items by Name to ensure that VLOOKUP operates properly.
Return back to spreadsheet with IIF export and enter the following formula to help with large update.
Column AW set header to MATCH_NAME enter following formula =VLOOKUP($B22,'[pricelist 10 01 18.xlsx]Table 1'!$A$2:$C$3034,1,FALSE)
Column AX set header to MATCH_PRICE enter following formula =VLOOKUP($B22,'[pricelist 10 01 18.xlsx]Table 1'!$A$2:$C$3034,3,FALSE)
Column AY set header to DIFF enter following formula =IF(M22-AX22<>0,M22-AX22,"")
Copy formula to all rows where you are going to update the price.
As a last row add total counts to validate variation =COUNT(AY38:AY9946)
Select all values in column AX and paste them as values only into column M.
Check totals in AY column if it is equal zero copy paste operation completed successfully. You might need to remove all filters prior to the copy operation. Once completed delete columns AW, AX, AY and use the next steps to save the file.
Once you’ve updated your IIF file:
Select File, and then Close.
Click Save when prompted to save your changes.
Click Yes when warned that you might lose features.
File must be be saved in Tab Delimited TXT format. Rename to IIF extension if saved explicitly
The final step is to import the revised IIF file back into QuickBooks:
Select File.
Select Utilities.
Select Import.
Select IIF Files.
When the Import window appears, double-click the IIF file that you edited in Excel.
A prompt will inform you when your data has been imported. Click OK.
Page updated
Google Sites
Report abuse