The Dimension Excel Add-on

Overview

Dimension allow you to copy measurements to the clipboard or to drag and drop them to other applications. It also allows you to drag and drop Quantity Worksheet items to other applications. Normally the measurement and item quantities are transferred as the actual quantity as of the time it was transferred. The disadvantage of this is that if the quantity changes later, the transfer needs to be repeated.

For most applications that is the best we can do. But for Excel, the Dimension Excel Add-on allows us to transfer a formula that goes back to Dimension periodically to get the latest quantity. The Add-on provides three functions for use in Excel formulas, one for measurement quantities and two for item quantities. We'll explain the functions in detail later. The functions are evaluated when they are first added to the Excel workbook and whenever the workbook is re-opened. They can also be re-evaluated on command.

Imagine that you have finished your preliminary takeoff and wish to see the cost extensions. You transfer worksheet items (or measurements) to your Excel workbook. Later you receive an addendum for the project and some of the quantities change. With the Dimension Excel Add-on you simply re-open the workbook and the updated quantities appear automatically.

The add-in also provides a drill-down capability. Simply right-click on a cell containing a Dimension function and pick "eTakeoff Dimension Drill Down" from the context menu. Dimension will start (if necessary), open the referenced project and drawing or quantity worksheet and highlight the measurement or item referenced in the formula.

Add-on Formula Functions

If you're familiar with Excel, you know that the value of a cell can be calculated from a formula. Formulas include functions such as SUM. The Add-on provides three functions that retrieve measurement or item quantities from Dimension projects.

ETkoMeasQty(MEASUREID, QTYTYPE) -  This function retrieves a measurement quantity. The MEASUREID parameter is a quote-enclosed string containing a measurement identifying code. These codes are created internally by Dimension and are simply a way to uniquely identify a measurement. The QTYTYPE parameter is also a quoted string ("Area", "Count", "Length", "Perimeter" or an extension varible name) specifying which quantity you wish to use from the measurement. An actual cell formula for this function might be:

          =ETkoMeasQty("463.125","Area")

The "463.125" means project 463, measurement 125. You don't have to know or understand these numbers as we'll explain later.

ETkoWksQty(ITEMID) -  This function retrieves a Quantity Worksheet item quantity. The ITEMID parameter is a quote-enclosed string containing an item identifying code. These codes are created internally by Dimension and are simply a way to uniquely identify a quantity worksheet item. An actual cell formula for this function might be:

          =ETkoWksQty("463.78")

The "463.78" means project 463, item 78. You don't have to know or understand these numbers as we'll explain later.

ETkoBidQty(BIDCODE) -  This function retrieves the total of all Quantity Worksheet item with the specified bid code. The BIDCODE parameter is a quote-enclosed string containing the bid code. An actual cell formula for this function might be:

          =ETkoBidQty("03-1100")

ETkoMeasBidQty(BIDCODE, QTYTYPE) -  This function retrieves the total of QTYTYPE across all measurements in the project with the specified bid code. The BIDCODE parameter is a quote-enclosed string containing the bid code. The QTYTYPE parameter is also a quoted string ("Area", "Count", "Length", "Perimeter" or an extension varible name) specifying which quantity you wish to use from the measurement. An actual cell formula for this function might be:

          =ETkoMeasBidQty("03-1100","Length")

Uniquely with the bid code functions ETkoBidQty and ETkoMeasBidQty, they do not include a project specification. Instead, the project is selected from the Dimension menu as we'll describe later. This means you can create an Excel template that uses ETkoBidQty and/or ETkoMeasBidQty function instead of the other functions. You can then create a new workbook from the template for a new project. You select the new project and the item and/or measurement quantities are transferred automatically. No more dragging and dropping for each project.

Adding Dimension Formulas to a Spreadsheet

The best way to add a Dimension formula to a workbook is to copy/paste the quantity or to drag and drop a measurement or quantity worksheet item. That includes the following operations:

• Copy measurement quantities to the clipboard in Dimension then paste them in Excel.
• Drag measurements from a Dimension drawing and drop them into Excel cells.
• Select cells in the Dimension Measurement List then drag and drop them into Excel. Only the quantity column can be transferred as a formula. All others are transferred as values.
• Select cells in the Dimension Quantity Worksheet then drag and drop them into Excel. Only the quantity column is transferred as a formula. All others are transferred as values.
• Drag quantities from the Control Panel Quantity List. If the quantity you want is being editing, You need to click down on the quantity description rather than the quantity. (Otherwise the quantity editing logic will gobble up the mouse activity.)

All these techniques can be used to transfer Excel formulas OR to transfer actual values. Since Dimension doesn't know where the values are going, you must set the transfer mode for values or Excel formulas (links).

• On the Measurement Toolbar the Copy/Drag Excel Links button should be down (pressed) to transfer links and up to transfer values.
• On the Measurement List the Copy/Drag Values or Excel Links control should properly set. Changing this setting changes the Copy/Drag Links button in the Measurement Toolbar (and vice-versa.)
• On the Quantity Worksheet the Copy/Drag Values or Excel Links control should properly set. Changing this control will NOT change the other two controls. You can drag values, drag ETkoWksQty formulas or drag ETkoBidQty formulas.

Manipulating Excel Formulas containing Dimension Functions

Once you created a formula by transferring from Dimension, you can manipulate as you wish. EXCEPT: Do not change the MEASUREID parameter or the ITEMID parameter! Possible changes include:

• Expand the formula to add various factors. For example, if you had a measurement in square feet but wanted the cell value in square yards, you could modify the formula to:
            =ETkoMeasQty("463.125","Area")/9.0
• Copy or move the entire formula to another cell.
• Copy and paste a single Dimension function into another cell. For example, you could multiply an area measurement by a height measurement:
            =ETkoMeasQty("463.125","A")*ETkoMeasQty("463.87","L")
  WARNING: You can only drill down on the first Dimension function in a formula.
• Change the second parameter of the ETkoMeasQty function to get a different quantity (Area, Count, Length or Perimeter) for the same measurement.

Selecting the Current Project

As described above, the ETkoBidQty and ETkoMeasBidQty functions use the Current Project. To select the current project, select "Dimension/Select Current Project" from the Excel main menu. A list of projects will be displayed. Double-click on the desired project.

Getting the Latest Values into Excel from Dimension

When you first add a Dimension formula to a workbook, the current value is loaded from Dimension. That value is re-used as long as the workbook remains opened. When you re-open the workbook the current value is again loaded from Dimension.

If you wish to re-load values from Dimension while the workbook it opened, you can use the Excel main menu option "Dimension/Refresh Quantities from eTakeoff".

Drilling Down from Excel to Dimension

It's impossible to tell what measurement or quantity worksheet item a formula refers to from the formula itself. So the Dimension Excel Add-on provides a drill down capability. Simply right-click on the cell and select "Dimension Drill Down" from the context menu.

Dimension will start (if necessary), open the referenced project and drawing or quantity worksheet and highlight the first measurement or item referenced in the formula.

WARNING: The ETkoBidQty function returns the sum of all quantity worksheet items with the specified bid code. If you have multiple items with the same bid code, drilling down will highlight the first one.

Find Missing Project

When an ETkoMeasQty or ETkoWksQty formula is added to the workbook, extra information is added to the workbook to identify the project if it is exported, deleted, then imported.

If you copy cells to a different workbook, this information isn't transferred. Therefor the formulas may return the value "Project Deleted" because they can't find the new imported project.

To fix this situation, select "Dimension/Find Missing Project" from the Excel main menu. A list of projects will be displayed. Double-click on the desired project.