SQL to Excel

**gazy007** · 04-20-2015, 11:25 AM

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.

One sheet has got all rows from SQL table and Formatted sheet has got extra columns we use.

We could refresh the Rawdata columns and formatted columns sync with Rawdata worksheet. But when an extra line or product added to previous order id then

formatted columns go out of sync for example

Id ProductName Manual column
2 Tyre Refund
3 Tubes Faulty
4 Tools Exchange

When another product that has two orders and added on the system

Id ProductName Manual column
2 Tyre Refund
2 Tyre Tubes Faulty
3 Tubes Exchange
4 Tools

It is a bit complicated to explain as well. Thanks in advance or let me know if I have not made any sense.

**GeneralDisarray** · 04-20-2015, 11:32 AM

What do you mean by "out of sync" exactly?

Also, does your second table look exactly like the first table - except for the extra columns (more specifically, does it have the same number of rows, with just more column details?)

You might try just adding the calculated columns to the right hand side of the query table. They should remain there upon refresh - I think you need to adjust the "connection properties" to keep the column order or something like that. I've done that in the past, but not for some time.

**gazy007** · 04-20-2015, 12:06 PM

That is what I need not sure where to start.
at the moment I have two worksheets one is from sql and the second copy of first worksheet and my calculation. Is there a way you could recall how to fix it?

Thanks,

**GeneralDisarray** · 04-20-2015, 12:55 PM

Can you post an example of the file in some way?

Basically you can just right-click-insert columns to the query table, then author the formulas as needed in the new columns.

NOTE: This is not using a second table - I'm talking about adding to the query table. After you add the columns, click somewhere on the query table so you get the "Table Tools" area to show up in the ribbon. Go to 'Properties' in the "External Table Data" area and check the box to preserve column order. That should make the changes (new columns you added) persist upon refresh.

**gazy007** · 04-20-2015, 01:28 PM

What would you need? Excel file or sql querying?

I could upload it tomorrow for you.

**GeneralDisarray** · 04-20-2015, 03:48 PM

Ok, I guess just the file you have with the two different tables. I think I could put the formulas into just the one (query) table and send it back to you to try a refresh.

**gazy007** · 04-21-2015, 04:57 AM

I have attached the file and thanks.

**gazy007** · 04-21-2015, 03:27 PM

Any luck with it yet?

**GeneralDisarray** · 04-27-2015, 07:59 AM

hello again, I don't see an attachment. Can you try again?

**gazy007** · 04-27-2015, 12:17 PM

Hi,

I have attached it again.

**GeneralDisarray** · 04-27-2015, 02:50 PM

Ok, try refreshing the query table - the formulas I added to columns O and P on the far right, should refresh with the table.

**gazy007** · 04-27-2015, 03:28 PM

is it possible to add just text on the new columns such as how much time it took to inspect and the labour cost and each row should sync with credit request number row. It would avoid mistakes or accidents on the spreadsheet.