Importing from another worksheet problems.

Hi. Hello everyone. I'm looking forward to being active in this forum.

In the meantime I have a data import problem.

A database is generating an excel workbook (called "postings.xls"). I have no control over the DB method of export. The data is simply dumped in a sheet with no headers. The exported data is always in the same format & column locations but the column lengths can be any number of rows. I am only interested in the information in column H from the postings sheet. My problem is when I import the whole postings.xls sheet into a table in my new worksheet (RevenueChecker.xlsx). The new table for some inexplicable reason changes the position of the columns. For example the original column H import can end up in column I one day and then column P the next, I've had the information I want appear in virtually any column from B to I.

All I want to do is create a new imported sheet, within my RevenueChecker workbook, which is an exact copy of the exported postings.xls sheet. I've checked all the DB exported postings.xls sheets and the info I need is always in column H. Yet as mentioned, once imported into my RevenueChecker it can appear in any column. I have tried creating links direct to the postings.xls workbook, column H, but again the problem is that the update is hit and miss and I end up with #ref errors. So to summarise, I need to create a foolproof link to the info in postings.xls, column H, which is available to my RevenueChecker.xlsx workbook.

The workbook (as mentioned), is automatically generated by a proprietary DB each night. The RevenueChecker workbook is then opened by my colleague who is IT illiterate. The RevenueChecker (which I have created) is returning spurious data because #ref errors are produced when the location of the column H data cannot be found consistently.

Any help on this problem would be greatly appreciated.

1. Just the data in H is needed.
2. Manually (Via the DATA Connections facility).
3. Sanitised copies attached. (The checktender_detail_0.xls referred to in the worksheet, I have no issues with.

A down and dirty solution would be to use iferrror. Something along the lines of:

Formula:

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(A1+B1,A1+C1),A1+D1),A1+E1),A1+F1),A1+G1),A1+H1),A1+I1)

In my sheet (see image) I added the value in A1 to each of the other columns until I did not get an error.excel-medic-import-issue.png

I've just discovered something:

On first opening the RevenueChecker.xlsx the postings.xls imported as a table within the RevenueChecker. On the first time of opening it, a few hours ago. The postings.xls Column H appeared in the new generated table (Sheet1) in column I. On opening RevenueChecker.xlsx a second time just now, postings.xls, Column H magically had moved within the table to column H. Truly odd that. On the third opening of RevenueChecker.xlxs, magically Column H of the imported postings.xls now appeared in the table within RevenueChecker.xlsx as column I again! It's exactly the same postings.xls file which is being imported, so why on earth does it change columns within the RevenueChecker.xlxs imported table?

Yes, I tried that. getting the imported data to update automatically was very hit & miss.