Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •