Excel 2010: New column in spreadsheet doesn't associate with data

[joek]

I have a spreadsheet which pulls data from a database (I cannot manipulate the database itself).

I added a column (column A "Status") to the worksheet where I manually input a status for each row/record - this is for my own records and I don't want this information on the main database.

When I refresh the data or sort the data, column A remains static, meaning that the information in column A no longer relates to the correct rows/records. (No frozen panes etc.)

I tried inserting a new column into the spreadsheet at column B, instead of column A (so my Status column is inside the data, not next to it). Now, when I sort by any column, the new status column sorts together with it. But when I refresh the data, I have the same problem as before.

How can I add a column to the spreadsheet so that the information I enter into it remains associated with the rows/records of the refreshed data?

[rory]

I don't think you can. What you might do is create a separate table where you add the comments and link that to the main table via an ID column of some sort. You should then be able to use lookup formulas adjacent to the table to return the right comments.

[joek]

Thanks for your reply. In the interest of time, I decided to take a "dumbed down" approach:

Data Sheet: Contains the raw data pulled in from the database
Master Sheet: Contains the data (paste values only) + my additional status column
Results Sheet: Each cell contains the following formula: =EXACT(Master!F1,Data!E1) – the difference in the column letters is due to the additional status column in the Master sheet. The result in each cell is either TRUE or FALSE.

I have conditional formatting on the Results sheet to highlight all FALSE results. So now I have identified where the discrepancies are in the data I have and in the data downloaded from the database.

What I want to do now is find an easy way to update the data in the Master sheet.

The “manual” way to do this is to (one-by-one) highlight the FALSE cell, note the cell where there is a discrepancy, go to the Data sheet, copy the new info, go to the Master sheet, find the corresponding cell and paste the updated information.

Since there are currently 192 rows and the columns go up to AO, this is somewhat difficult and time-consuming.

1. Best case scenario: an automagic way to update only the cells that are different, taking the info from the cell in the Data sheet and updating the corresponding cell in the Master sheet.

2. Second-best scenario: a table on another sheet, which I will call "Diff" with the following:

• Only info from the Results sheet where the cell value = FALSE
• Summary information, including:
  
  • The cell reference where there is a discrepancy in the Data sheet
  • The cell reference where there is a discrepancy in the Master sheet
  • The information in the Data sheet that needs to be copied over to the corresponding cell in the Master sheet

This would make it easier to copy/paste the updated information to the Master sheet.

Only problem is, I have no idea how to do either of these solutions :-)

If anyone out there can help me with this, I will be most grateful!

Thanks,

JoeK

[sdckapr]

I think Rory's solution is the method you want. Could you attach example files which detail what you have and what you want including a before update and an after update so we can understand it better?

Steve