Excel formulas and the GISconnector

The central function of Excel – in contrast to that of a database – is its capacity to make use of formulas that can be entered into cells as a dynamic equation. For this reason we have given particular consideration to the concept of “Formulas” in the development of the GISconnector for Excel. It is important to understand how the GISconnector for Excel operates with formulas.

Requirement

A connection between an ArcGIS Feature Class or Table and an Excel table must be available. If you have not yet created a connection click on the button

"Create connection" in order to establish a connection from the ArcGIS or Excel side.

You can see an abstract from an Excel table which is connected to a Feature Class. The option "Show formulas" has been activated under the index "Formulas". In column E a formula has been inserted; in this case it is the difference of the numbers in column C and column B.

GISconnector for Excel recognises if a formula has been entered for a column. In the dialogue

"Edit connection" under the index "Columns/Fields" you can see in which column a formula has been entered.

The concerning column, in this case “Difference”, is marked in pink. In the given case the column does not yet exist in ArcGIS.

To what extent does a formula in an Excel column influence the behavior of the GISconnector for Excel?

We assume that a formula in Excel is used in order to calculate a specific result dynamically. For this reason it is not possible to overwrite a column that contains formulas with values by transferring data from ArcGIS to Excel.This means, that if the relevant column in the "Columns/Fields" index of the

"Edit connection" dialogue is managed by the GISconnector for Excel it has no effect in case a formula has been assigned to this column in Excel. Under no circumstances the formula is overwritten when transferring from ArcGIS to Excel.

However if you would like to overwrite the column containing a formula with values from ArcGIS, copy the column containing the formula into the clipboard and then insert it again using the option “Insert values". Now the Excel column no longer contains a formula, but only the last calculated values. The concerning column is no longer marked in pink under the "Columns/Fields" index of the dialogue

"Edit connection" and can now be overwritten with values from ArcGIS at any time.

Important advice:

Columns with formulas can be deleted when transferring from ArcGIS to Excel. Read further about this in the chapter "Deleting rows and columns".