Sort by Date

[EDMONd]

My last question was answered so promptly and accurately I have decided to give it another try. I am trying to sort data by date to create weekly production tracking sheets. I have created 52 sheets for the weeks of the year and would like to send the info to the right sheet by the date which is entered on daily production. Any thoughts?
EDMONd

 

[EDMONd]

Ill get this yet.

Machines by Number.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	PARTNUMBER				OPERATOR		SET-UPBY			BASEPERDAY			QUALITY
2					Axel		Bandy			3600			100
3					OP#					CYCLETIME
4	MACHINE						TOTAL			8
5	27				ORDERQTY		87			WORKHOURS		PROD.%
6										8
7	DATE				QUANTITY		REJECTED					DAY	RUN
8	********************************************************************************************************
9												0	2.4
10					87							2.4
11												0
12												0
13												0
14												0
27

 

[EDMONd]

will it work again?

Production by Name.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1		Jorge		Tony		Axel		Angel		Adam		Abiel
2	1	#DIV/0!	8	#DIV/0!	25	#DIV/0!	33	#DIV/0!	60	#DIV/0!	23	#DIV/0!
3	2	#DIV/0!	9	#DIV/0!	27	2	40	#DIV/0!	61	#DIV/0!	67	#DIV/0!
4	3	#DIV/0!	10	#DIV/0!	28	#DIV/0!	47	#DIV/0!	62	20	69	#DIV/0!
5	4	#DIV/0!	11	#DIV/0!	30	#DIV/0!	48	#DIV/0!	63	#DIV/0!	70	#DIV/0!
6	5	#DIV/0!	12	#DIV/0!	31	#DIV/0!	49	#DIV/0!	64	#DIV/0!	71	#DIV/0!
7	6	#DIV/0!	13	#DIV/0!	72	#DIV/0!	50	38	65	#DIV/0!
8	7	#DIV/0!	15	#DIV/0!			51	#DIV/0!	66	#DIV/0!
9	20	#DIV/0!	19	#DIV/0!			53	#DIV/0!
10	21	#DIV/0!	24	#DIV/0!			55	#DIV/0!
11
12		#DIV/0!		#DIV/0!		2		38		20		#DIV/0!
37

 

[parry]

So your problem is really about fixing the #Div/0 error? See the Excel Help topic 'Correct a #DIV/0! error'. An extract from thsi topic states...

"Prevent the error value from displaying, using the IF worksheet function. For example, if the formula that creates the error is =A5/B5, use =IF(B5=0,"",A5/B5) instead. The two quotation marks represent an empty text string. "

Ideally you should correct this in all your source sheets (see why having 72 sheets may not be a good idea) or alternatively use an if statement looking for ISERROR and put in 0. If there is an error, this will affect your efforts in trying to sum and average results.

eg for cell B2 in your example ...
=If(Iserror('[Machines by Number.xls]1'!$M$9),0,'[Machines by Number.xls]1'!$M$9)

 

[EDMONd]

I have corrected all but 1 of my #div/0 errors. The last formula I need to fix is this....=SUMIF(L9:L40,">0")/COUNT(E9:E40:G9:G40)...
It is enevitable that I will have a 0 somewhere in this mess of cells. Can someone correct this for me. I have been trying with the IF worksheet function without success. My real issue is still sorting by date
Thank you
EDMONd
PS This formula is in cell M9 on the first sheet I posted.
EDMONd

 

[parry]

Hi, this is probably what you want...

=IF(COUNT(E9:E40:G9:G40)=0,0,SUMIF(L9:L40,">0")/COUNT(E9:E40:G9:G40))

If your DiV/0 totals are all numbers how are you going to sort by date. Where are the date fields? If your wanting to sort why not use Data\Sort on each column?

 

[EDMONd]

My dates are A9:A40. The corresponding counts are E9:E40.
Can Data/Sort be used for dates? The formula that you posted earlier ....=If(Iserror('[Machines by Number.xls]1'!$M$9),0,'[Machines by Number.xls]1'!$M$9)
.... worked like a charm.
Thanks EDMONd
PS I'm getting closer.

 

[parry]

Yes, Data|Sort can be use for dates. Make sure you select all columns and not just the date ones before selecting sort.