Import changes named ranges

[ICE9]

I have an issue with named ranges on an imported list. Excel change
the ranges on an import but not the adjacent columns. There ar
several issues wich complicate this.

Excel 2003

1. The data is imported from a csv file (not a problem)
2. Named ranges are needed for each column of the import and tw
additional formula columns to the right (not a problem if they stay th
same length)
3. There are 2 formula columns after the imported data
4. The import is configured to copy down formulas in the adjacen
columns.
5. Array functions refer to the named ranges to calculate values o
another sheet.
6. All named ranges must be the EXACT same length for the arra
functions to work.


Whenever I refresh the data for the import, Excel automatically change
the named ranges to include the new data, and copies the adjacen
formulas. It copies the adjacent formulas correctly but does no
change the named ranges associated with these formulas outside of th
import area. This causes the named ranges within the import to b
different from those of the adjacent formula columns and breaks al
array functions which refer to both.

Is there any way to make Excel change the named ranges of the adjacen
formulas when it copies them down?

I tried specifying named ranges that exceeded the size of the impor
for all columns, but when i refresh the data it changes them back t
end at the last row of the import. Once again, it does not affect th
adjacent columns, causing the ranges to be different lengths.

I can manually modify the ranges, but this document is automated fo
use by the management. They need to be able to press the macro butto
and let it do the rest

 

[ICE9]

Ok, well, i may have answered this one myself. However it still isn'
perfect

It seems that when you use "Overwrite existing cells with new data
clear unused cells" in the data import, it does not change the dat
ranges. This allowed me to extend all of the data ranges well past th
data and they don't change.

Unfortunately, if the data exceeds the range i choose then it will no
calculate any rows past my data range. I don't want to extend it to
far since this causes Excel to calculate NUMEROUS extra rows of blan
data. Since it already calculates 30 array formulas, which each cove
3,000 to 8,000 rows of data, that can be problematic and VERY slow.

I would prefer a solution which modifies all ranges to fit the data

 

[Dave Peterson]

Could you import to a different sheet, then copy|paste special|values (maybe
column by column, even??).

or import your data and then reapply the formulas to just the rows that need
it???

 

[ICE9]

Unfortunately the document is too large to use separate pages fo
import. There are also 11 Worksheets which do this same thing withi
the Workbook.

The real problem is that i'm trying to get Excel to do everythin
without any interaction from the user. Just one click of a butto
needs to do it all.

There is a limitation of the program that exported the csv files o
only 8,000 records so i have decided to use the named ranges for th
full length. This results in 88,000 records to be searched, but I nee
an excuse for a faster computer anyways

Thanks for the help though.