Using Formulas and Functions in Excel and BarTender
You have connected an Excel spreadsheet to your BarTender document using the Database Connection Wizard, and some of your data does not appear in BarTender.
Alternatively, you may also experience a great deal of lag when loading your BarTender label, establishing a connection with the Excel sheet, or when printing the label.
BarTender 2016 and later
BarTender will not be able to read database fields referencing external calculations or filtering done on them. This is a limitation of the database drivers for Microsoft.
Excel functions like VLOOKUP are very useful within Excel, but applications external to Microsoft Office may not be able to pick up the results of those tools into their application.
If you would like to continue to use your Excel spreadsheet to do all the necessary filtering and calculations, you can use the following workarounds to make the data available for BarTender.
This workaround consists of exporting the table to a secondary table. This way, the results are placed in a static table with no Excel formulas or functions acting upon it, making it easier for the Microsoft database driver to read.
Once you have finished processing your data in Excel:
- Click on File> Export
- Select a file type for a static table (Ex.: CSV or a tab-delimited text file)
- Choose Save As
- Select a location and Save the File.
- Connect this newly created File to your BarTender Document using the Database Connection Wizard.
- The data should now be visible from your BarTender document
Manually Copy Data
This workaround involves copying the excel sheet values to a new excel sheet to avoid using Excel formulas and functions. This dramatically decreases the lag.:
- Open the Excel sheet that contains the formulas and functions in Microsoft Excel.
- Select the cells or ranges you wish to copy.
- Select the Home tab.
- Select Copy in the Clipboard section.
- Create a new blank Excel sheet.
- Select the cell you wish to paste your values to.
- Select the lower half of the large Paste button. From the extended menu that appears, select Values.
- Save the sheet and connect to it from BarTender.