SQL to Excel

[gazy007]

Hi, I need help regarding a query I run from MS SQL and pull the data in Microsoft Excel 2010. I have inserted another worksheet so one of our user can add more columns to analyse cost or reason for returns/failures. When return department add another line to an existing data in MS SQL and the other user refresh the excel spreadsheet then manually added columns go out-of-sync. Is there anyway when a user refresh the RAWDATA or any changes on SQL table then it should sync my formatted worksheet. It is a bit complicated to explain as well. Thanks in advance or let me know if I have not made any sense.

<tbody>
</tbody>

 

[mole999]

does each record have a unique key, if so you could use a vlookup, localised in the workbook against new data runs

 

[RonB1111]

The short answer is no.

A workaround would be to enter the cost reasons on a different sheet (or a different section of the rawdata sheet) and also enter a linking id (eg: unique return id or invoice) - then use a vlookup or index/match formula to bring this info into the columns to the right of the rawdata. If the user is very attentive to always following the procedure it will work ok, otherwise you wind up with a mess similar to what you're currently dealing with.

Alternatively, sometimes you can enter the reasons directly into your SQL db and then just include those fields in your excel query. When this option is available, the disadvantage is it's much more time consuming to switch to the SQL entry screen and enter this input, so it often doesn't get done especially if more than one user is involved.