Refreshing external data, keeping it lined up with the rest of the sheet

[triptik]

This may be a very basic question, but I haven't been able to find an answer and I was hoping the Excel wizards on these boards could help me out.

I am using Excel 2007 on Windows XP Pro.

The goal...

I am working with a team that uses a proprietary application for issue tracking. I need to track more information than is available in the tracking application, but no one else on the team needs this info. The program has a "Send to Excel" feature that creates a new Excel spreadsheet, and I want to use that to update an Excel spreadsheet with my extra information.

What I've Done

I open the tracking program, Send to Excel, and it creates a brand new Excel spreadsheet, which I save to a specific file name - for example, ExportData.xlsx

Then I created a new spreadsheet - Tracking.xlsx - and created a new connection to the ExportData.xlsx file. I inserted the external data into A1 on my new spreadsheet. So far so good - several hundred rows of data.

I created new columns, some within the data table (they're shaded blue, just like the rest of the data), and some outside (no shading, to the right of the table). I've tried it both ways just to see if it makes a difference.

The Problem

For all my new columns (within and outside of the external data range), when I refresh the data and the number of rows have changed (for example, a record was deleted or added in the source), the content of my added columns no longer lines up with the original data. The values in my new columns remained in the same cells, but the external data shifted around them.

So, if I entered "45" in cell T12, then refreshed the external data and a new row was inserted above 12, "45" is still in T12, but the rest of the record is now on row 13.

I checked the help for Excel 2007, and under the heading "Specify how new data is added to an external data range", it tells me that if I select "Insert entire rows for new data, clear unused cells", then "When one or more rows are added in the data source, cells directly under and to the right of the external data range move down."

That option is what I have selected, but it doesn't seem to be working as described.

What am I missing? Is there a better way to do this?

Thanks!

 

[Fazza]

The data you enter is not related in anyway to the external data, as far as Excel is concerned. So, the external data refresh is independent of whatever entries you have made. If you operate like database you will be ok.

Such as, from the queried data and your additional fields, make a new, separate table. Use whatever fields you need from the queried data so that you can recreate the results if you started with the original external query and this new table. (Maybe there is a field that is unique for every record, so you would take that field from the external data and all the fields you've added.) Now that you have this new table, make sure you refresh it before refreshing the external data query. With the external data refreshed, re-instate your additional columns from the new table.

The same question came up a few times about a month or more ago; I'll have a look for those threads.

 

[Fazza]

These might help?

http://www.mrexcel.com/forum/showthread.php?t=376984

http://www.mrexcel.com/forum/showthread.php?t=381918

http://www.mrexcel.com/forum/showthread.php?t=384086

 

[triptik]

I wasn't looking at this as a query, but treating them that way does open up some more options I hadn't thought about. It will also give me more useful search terms

I'll definitely try this out.

Thank you very much!

 

[Fazza]