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!
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!
