Formula based off a connection not working

[julrikson]

I have a connection set up to a text file, this text file gets updated every day and the # of rows changes daily. I have a concatenate formula for each row of the imported text file plus I have the formula going down extra rows to compensate for extra rows that may be in the text file. The problem is that will work one day, but the next day when I go to refresh I will get #refs or blanks down towards the bottom few rows. I can auto fill the formulas from the top and it takes care of it but I am trying to figure out if their is a way to fix that problem so I don't have to fix it every day.

This might be a clearer example of what I am trying to do -

i have a text file that gets imported, name goes into B and Date goes into column C
In column A I have =concatenate(A1,B1). When I set it up it pulled in 10 rows and I drug the formula in A down to row 50 to be safe. The next day I refresh and the file is 20 rows, if I click on row 11, the formula now says concatenate(A21,B21) when it should be concatenate(A11,B11) so the result is blank because the data is only 20 rows, it basically bumps the formulas up however many rows were added. But rows 1-10 still work, if the file is less then before is when I get the #ref's

Any ideas?

[masteff]

Did you set it up using "import external data"? If so, you need to change the "External Data Range Properties"... to do so, click in the upper left cell of where your import goes, do a right-mouse-click and select "Data Range Properties", then toward the bottom change from "insert..." to "overwrite..."

(answer based on Excel '03, your options might vary slightly)