Manual Entered Data On Excel Ms Query Is Misaligned After Refresh

I have done an MS SQL Query in excel. I have added extra colums in the excel sheet which I want to enter manual data in. When I refresh the data, these manually inputted columns become misaligned to the imported data they refer to. Is there any around this happening. I have tr开发者_开发百科ied to link the imported data sheet to a manual data sheet via vlookup but this isn't working as there are no unique fields to link together. Please help! Thanks

Excel version is 2010. MS SQL version is 2005. There is no unique data. Because excel firstly looks like this.

when we entered a new order in to database Excel looks like this

---

Try this: in the External Data Range Properties, select "Insert entire rows for new data".
Not sure, but worth a try. And keep us updated of the result !
edit: And make sure you provide a consistent sort order.

---

There is no relationship to the spreadsheets external data and the columns you are entering. When refreshing typically the data is cleared and updated though there are other options in the external data refresh menu you could play with. You could play around with the External data options in the menu to see if changing the settings on what happens with the new data would help.

If you want your manually entered data to link to the data in the embedded dataset, you have to establish the lookup with a vlookup or some formula to find the rows info and show it.

Basically you are thinking the SQL data on the spreadsheet is static, but it isn't unless you never refresh it or disconnect it from the database

---

1. note that Marcel Beug has given a full solution to this problem in a more recent post in this forum @ Inserting text manually in a custom column and should be visible on refresh of the report
2. he has even taken the time to record an example in a video @ https://www.youtube.com/watch?v=duNYHfvP_8U&feature=youtu.be