Complicated ODBC data refresh question.

[JDNETTC]

Hi,

I am having trouble when refreshing data for my spreadsheet. The refresh
adds in a new row or more, which have 5 associated data columns. I have
another 20 columns beyond that where I have IF formula's performing
calculations.

A B C D E F-------------AB
Data from ODBC =IF(AND($D6>"039081",$D6<"039113"),$E6, 0)

there are different formulas to break out different data for each column.

My problem is that when I refresh the ODBC data, it updates Column A-E no
problem, but the bottom row will not include my formulas anymore:

A B C D E F--->AB
Row 1 Old data Formulas
Row 2 New data Formulas
Row 3 Old data which was pushed down ----------Nothing! help!

I have to much data to fix the bottom row each time I update my data.
Any suggestions how to keep my formulas connected to the appropriate row
during data refresh?

Thanks

John

 

[Conan Kelly]

JDNETTC,

What version of XL are you using?


For XL 2002/2003:
--Make sure your "External Data" toolbar is showing.
--Selelct a cell within your external query data (buttons on External Data
toolbar should be enabled)
--Click the "Data Range Properties" button on the External Data toolbar.
--Down at the bottom of the "External Data Range Properties" dialog box,
there are 3 option buttons and a checkbox below that.
--I think I would try the "Insert entire rows for new data, clear unused
cells" option.
--Reguardless of which option you choose, make sure the "Fill down
formulas..." check box is checked.

You might have to expierment with these settings to see which ones will give
you the desired results.

HTH,

Conan

 

[JDNETTC]

2007

JDNETTC,

What version of XL are you using?


For XL 2002/2003:
--Make sure your "External Data" toolbar is showing.
--Selelct a cell within your external query data (buttons on External Data
toolbar should be enabled)
--Click the "Data Range Properties" button on the External Data toolbar.
--Down at the bottom of the "External Data Range Properties" dialog box,
there are 3 option buttons and a checkbox below that.
--I think I would try the "Insert entire rows for new data, clear unused
cells" option.
--Reguardless of which option you choose, make sure the "Fill down
formulas..." check box is checked.

You might have to expierment with these settings to see which ones will give
you the desired results.

HTH,

Conan

 

[JDNETTC]

I have actually tried what you suggested before in 2007. There is no "fill
down formulas" that I can see. I just tried it again, and it still populates
column A-E no problem, but my other formulas, which are not part of the ODBC
data, do not update the bottom row.

 

[Conan Kelly]

JDNETTC,

Unfortunately, I'm not familiar with XL 2007.

Maybe someone else here, who knows 2007, can be of more help. Sorry that I
couldn't,

Conan

 

[JDNETTC]

Is this a hopeless situation?

 

[JDNETTC]

I fixed my own problem. Since columns A-E were part of an excel table, they
were updating independently of my columns to the right. I simply grabbed the
bottom right corner of my table and expanded it all the way to column AB.
Now, when I refresh my data, everything updates automatically. Yay!