Importing Usable Formulas from ODBC

S

Semolar

Hi all,

I use networkdays extensively. I have a worksheet set up where the end
4 columns are used to do networkday type calculations. Data is imported
via odbc from Oracle. Each time I refresh the data, I am required to
copy the formulas to the newly added records. Its a PITA and there
must be a better way. Any ideas?

Thanks :)
 
A

Arvi Laanemets

Hi

When you activate any cell in query result table, then select 'Data Range
Properties' from menu Data>GetExternalData, or from right-click dropdown.
There check both 'Overwrite existing cells ...' and 'Fill down formulas ...'
..

You must have all formulas in columns, adjacent to result table - i.e. right
to result table, and without any gaps. Copy all formulas to same amount of
rows, as existing result table. From now on, whenever the query is
refreshed, the range with formulas is adjusted automatically.

A tip! When the query returns nothing, the result table contains only header
row. When you had formulas for data row only, there is no one left - and
they aren't restored later. To avoid such problem, I usually have all
adjacent formulas in form
=IF(ReferenceToTableColumn=ColumnHeader,FormulaHeader,MyFormula)
i.e. like this:
X1=IF(B1="Amount","Price",C1/B1)


Arvi Laanemets
 
Top