Importing Usable Formulas from ODBC

[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

[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


"Semolar" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Semolar
> ------------------------------------------------------------------------
> Semolar's Profile:
http://www.excelforum.com/member.php...o&userid=31589
> View this thread: http://www.excelforum.com/showthread...hreadid=512822
>

[Semolar]

Wow! Thank you very much Arvi