Import External Data doesn't import

Hi guys, I have a worksheet that I am trying to replicate in another workbook using the "import external data" menu command. Everything seemed fine when I used this, but when I looked closer I noticed that some cells were not appearing in the destination worksheet.

I have attached a sample of the source document. Open a new workbook and try and import yourself!

The dates in column F just won't import!! I've tried a few different date formats and I've tried splitting the merged cell in the heading for that column of data but alas, to no avail.

Would love to hear your suggestions.

J

Apart from the merged cells, it all imports correctly when I import it to a blank worksheet.

Actually I've just realised that my import doesn't include the headers for the date columns, presumably because they are text rather than dates. It might be best to name the data area (including the second row of headers) ie row 7 downwards in your source file and set the query to import the named range. This imports the data and headers correctly. You can add the first row of headers and any other rows above the query.

I am using Excel 2003. I was really hoping to avoid the use of ranges as the data in the source document will always be increasing. I have looked at 2 other solutions to my problem:

using INDIRECT
This is not quite what I'm after as it requires the source document to be open at the same time. It also seems to be a little slow at updating the amount of data I have.

Just using =
This links to every cell in the source. The problem with this is that changes to the source document (more rows of data, insertion/deletion of rows) can play havoc in the destination. It also shows blank cells as '0' - this can be defeated by use of IF and ISBLANK, but things are starting to look messy.

Essentially I want to import an exact replica of a worksheet into another workbook (which stays up to date)- why is this so hard? I've noticed numerous other people on this forum with a similar desire. C'mon microsoft - google will have you for breakfast if you can't sort out basic things like this!

Any other suggestions, good people?

j

You can name the whole area from the header row down, including all the blank rows. This will automatically import any new data rows.

Alternatively you can use a dynamic range - see http://support.microsoft.com/kb/830287

OK, I think I've discovered why my data wasn't importing. According to this post: http://www.dailydoseofexcel.com/arch...ed-data-types/
It's because my column of data had mixed data types in it. I had a text heading and dates underneath it. I'm assuming this is my problem. Still don't know how to solve it but at least I know.