SQL Link Refresh Scrambles Order

[jarett]

I have a spreadsheet that is linked to a SQL DB, it is refreshed each time I open it. The file could grow in records and column D is updated each time, I am entering quantities in column E & F, G&H are formulas. I planned on saving it each time I enter values to give me a running average. The first time I saved and reopened it refreshed and my entered values got reordered.

IM_WH_COUNT.xlsx
	A	B	C	D	E	F	G	H
1	Goal = 0% Excellent = 0.9%-5% Good = 5.1%-15% Average=15.1%-25% Needs Attention > 25.1%	Inventory Discrepence %	54%
2	ItemCode	ItemCodeDesc	WarehouseCode	QuantityOnHand	Qty on Shelf	WIP Additions	Updated QTY	Percent Difference
3	08-0003188	NO HOLES-31-1/2 X 500 .005 WHT	000	0			0
4	08-0007208	120 in.SPACING-31-1/2 x 500' .	000	3	2	0	2	33%
5	08-0007208	120 in.SPACING-31-1/2 x 500' .	003	0			0
6	08-0007209	34 in.SPACING-31-1/2 x 500' .0	000	8	2	2	4	50%
7	08-0007211	60 in.SPACING-31-1/2 x 500' .0	000	0	1		1	#DIV/0!
8	08-0007212	60 in. SPACING-19 X 500' .005	000	4			0
9	08-0007213	NO HOLES-19 X 500' .005 WHT OP	000	5	1		1	80%
IM_WH_COUNT

Cell Formulas
Range	Formula
C1	C1	=AVERAGEIF(H3:H5000,">0")
G3:G9	G3	=[@[Qty on Shelf]]+[@[WIP Additions]]
H3:H9	H3	=IF([@[Qty on Shelf]]= "","",ABS([@[Updated QTY]]-[@QuantityOnHand])/[@QuantityOnHand])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Cell Value	=0	text	NO
C1	Cell Value	between 0.009 and 0.05	text	NO
C1	Cell Value	between 0.051 and 0.15	text	NO
C1	Cell Value	between 0.151 and 0.25	text	NO
C1	Cell Value	>=25.1%	text	NO

 

[bobsan42]

Data in Databases is not naturally stored in a sorted order.
It would help if you describe how the connection to db is established and how the table is updated. But partially updating the table without explicitly stating the sort order may lead to totally scrambled data.

 

[jarett]

Data in Databases is not naturally stored in a sorted order.
It would help if you describe how the connection to db is established and how the table is updated. But partially updating the table without explicitly stating the sort order may lead to totally scrambled data.

It is connected by a direct connection, the table is updated through the ERP system (Sage 100) that the users use. The only way I can think of is linking the entries I make in the excel file and importing them against the record.

 

[Joe4]

If you want them sorted in a particular order, create a query in SQL that sorts them in your desired order, and then link to that query instead of the underlying table.

 

[jarett]

If I saved the file as a new file each time we preformed a count, is there a way to run a script to just pull cell C1's value into a new file to keep track of a running average?

 

[jarett]

If I saved the file as a new file each time we preformed a count, is there a way to run a script to just pull cell C1's value into a new file to keep track of a running average?

Retrieve Values from Many Workbooks - Excel UniversityI was able to solve this with this really good article.