Shorten formula

[CJS548]

Hi, this is a very simple request...
i know there must be another way around, but this is what i have based on my limited knowledge...
please shorten this formula for me:

=WORKDAY.INTL(N4,(ROUND(VLOOKUP((IF((IFNA(VLOOKUP(J4,$T$14:$T$21,1,0),"OTHERS"))=("RAW"),"SCR",IF((IFNA(VLOOKUP(J4,$T$14:$T$21,1,0),"OTHERS"))=("RAW D"),"SCR",IF((IFNA(VLOOKUP(J4,$T$14:$T$21,1,0),"OTHERS"))=("ALI SYDNEY"),"ALI",IF((IFNA(VLOOKUP(J4,$T$14:$T$21,1,0),"OTHERS"))=("ALTONA"),"VINNIES",IF((IFNA(VLOOKUP(J4,$T$14:$T$21,1,0),"OTHERS"))=("DANDENONG"),"VINNIES",IF((IFNA(VLOOKUP(J4,$T$14:$T$21,1,0),"OTHERS"))=("SHEPARTON"),"VINNIES",IF((IFNA(VLOOKUP(J4,$T$14:$T$21,1,0),"OTHERS"))=("ACR"),"ACR","OTHERS")))))))),$T$7:$Y$10,6,0),1)),11,$AC$14:$AC$49)

actually the purpose of this formula is to see the cell is for what item.
VINNIES is a category consists of DANDENONG, ALTONA and SHEPARTON. etc etc for the rest...
and if the cell is dandenong, it will return vinnies, then base on the category vinnies, i will have another table that states how many days i need for vinnies.
then the cells need to return the days needed, which will be added into this formula as well, using vlookup.
i know its a bit messy... but this is how i can describe it...
upload an image to help clear things up.
thanks!

 

[hernantorres23]

Hi, your attachment is difficult to read, In addition I see that you can't show some data.
Please try prepare a data example using add-in specifically for this and it can be found here XL2BB
Pay attention to this post XL2BB 2 Squares

 

[CJS548]

￿￿

 

[CJS548]

Book1
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
6									OTHERS			1300			1
7	CTYD	RAW D	10/12/2019			10	88		SCR	SCR	11	14300	19700	0.73	1.38
8	CTYD	ACR	16/12/2019	6/12/2019	13/12/2019	16	3		ACR	ALI	3	3900	20300	0.19	5.21
9	CTYD	ALI SYDNEY	16/12/2019	5/12/2019	12/12/2019	16	4		ALI	VINNIES	3	3900	19800	0.20	5.08
10	CTYD	ALTONA	16/12/2019	28/11/2019	4/12/2019	16	12		VINNIES	ACR	3	3900	22500	0.17	5.77
11									OTHERS		20	26000
12									OTHERS
13									OTHERS
14									OTHERS	RAW				WORKDAYS:		24.00
15									OTHERS	RAW D
16									OTHERS	ALI SYDNEY
17										ALTONA
18										DANDENONG
19										SHEPARTON
20										ACR
21										OTHERS
28.11 (2)

Cell Formulas
Range	Formula
M7, M8:M10	M7	=J7-I$2
N7, N8:N10	N7	=IF(ISBLANK(L7),88,J7-L7)
U7:U10	U7	=S7/T7
V7:V10	V7	=T7/S7
L8:L10	L8	=WORKDAY.INTL(K8,(ROUND(VLOOKUP((IF((IFNA(VLOOKUP(I8,$Q$14:$Q$21,1,0),"OTHERS"))=("RAW"),"SCR",IF((IFNA(VLOOKUP(I8,$Q$14:$Q$21,1,0),"OTHERS"))=("RAW D"),"SCR",IF((IFNA(VLOOKUP(I8,$Q$14:$Q$21,1,0),"OTHERS"))=("ALI SYDNEY"),"ALI",IF((IFNA(VLOOKUP(I8,$Q$14:$Q$21,1,0),"OTHERS"))=("ALTONA"),"VINNIES",IF((IFNA(VLOOKUP(I8,$Q$14:$Q$21,1,0),"OTHERS"))=("DANDENONG"),"VINNIES",IF((IFNA(VLOOKUP(I8,$Q$14:$Q$21,1,0),"OTHERS"))=("SHEPARTON"),"VINNIES",IF((IFNA(VLOOKUP(I8,$Q$14:$Q$21,1,0),"OTHERS"))=("ACR"),"ACR","OTHERS")))))))),$Q$7:$V$10,6,0),1)),11,$Z$14:$Z$49)
S7:S10, S11	S7	=R7*$S$6
R11	R11	=SUM(R7:R10)
W14	W14	=NETWORKDAYS.INTL(X13,Y13,11,Z13:Z57)
P6:P16	P6	=IF((IFNA(VLOOKUP(I6,$Q$14:$Q$21,1,0),"OTHERS"))=("RAW"),"SCR",IF((IFNA(VLOOKUP(I6,$Q$14:$Q$21,1,0),"OTHERS"))=("RAW D"),"SCR",IF((IFNA(VLOOKUP(I6,$Q$14:$Q$21,1,0),"OTHERS"))=("ALI SYDNEY"),"ALI",IF((IFNA(VLOOKUP(I6,$Q$14:$Q$21,1,0),"OTHERS"))=("ALTONA"),"VINNIES",IF((IFNA(VLOOKUP(I6,$Q$14:$Q$21,1,0),"OTHERS"))=("DANDENONG"),"VINNIES",IF((IFNA(VLOOKUP(I6,$Q$14:$Q$21,1,0),"OTHERS"))=("SHEPARTON"),"VINNIES",IF((IFNA(VLOOKUP(I6,$Q$14:$Q$21,1,0),"OTHERS"))=("ACR"),"ACR","OTHERS")))))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L20:L22	Cell Value	<$I$2	text	NO
L13	Cell Value	<$I$2	text	NO
L9:L10	Cell Value	<$I$2	text	NO
L11	Cell Value	<$I$2	text	NO
L8	Cell Value	<$I$2	text	NO
W14,V7:Z11	Expression	=AND(V7<>"",ABS(V7-ROUND(V7,0))<=0.2)	text	NO
N4:N53	Cell Value	<1	text	NO
M4:M53	Cell Value	<7	text	NO
L4:L6	Cell Value	<$I$2	text	NO
L7	Cell Value	<$I$2	text	NO
L12,L14:L19,L23:L27,L29:L53	Cell Value	<$I$2	text	NO

 

[CJS548]

Sorry, please ignore the conditional formatting...

 

[hernantorres23]

Taking your example in L8, you can put this

=WORKDAY.INTL(K8,ROUND(INDEX(myOthers,MATCH(INDEX(myRange,MATCH(I8,myDetail,0),2),myCategory,0),6),1),11,myHolidays)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2
3
4								Table 1. Origin											01/12/2019	31/12/2019
5																	WORKDAYS:	24			11/12/2019
6																					25/12/2019
7								CTYD	RAW D	10/12/2019			10/12/2019	88
8								CTYD	ACR	16/12/2019	06/12/2019	13/12/2019	16/12/2019	3
9								CTYD	ALI SYDNEY	16/12/2019	05/12/2019	12/12/2019	16/12/2019	4
10								CTYD	ALTONA	16/12/2019	28/11/2019	04/12/2019	16/12/2019	12
11
12
13
14
15
16
17
18
19
20
21
22
23
24									Table 2.				a name (you can change this)	1300
25									RAW	SRC
26									RAW D	SRC
27									ALI SYDNEY	ALI		Table 3.
28									ALTONA	VINNIES		SCR	11	14300	19700	0.725888325	1.377622378
29									DANDENONG	VINNIES		ALI	3	3900	20300	0.192118227	5.205128205
30									SHEPARTON	VINNIES		VINNIES	3	3900	19800	0.196969697	5.076923077
31									ACR	ACR		ACR	3	3900	22500	0.173333333	5.769230769
32									OTHERS	OTHERS			20	26000
33
Sheet5

Cell Formulas
Range	Formula
R5	R5	=NETWORKDAYS.INTL(start_date,end_date,11,myHolidays)
M7, M8:M10	M7	=J7-I$2
N7, N8:N10	N7	=IF(ISBLANK(L7),88,J7-L7)
L8:L10	L8	=WORKDAY.INTL(K8,ROUND(INDEX(myOthers,MATCH(INDEX(myRange,MATCH(I8,myDetail,0),2),myCategory,0),6),1),11,myHolidays)
P28:P31	P28	=N28/O28
Q28:Q31	Q28	=O28/N28
N28:N31	N28	=M28*base_value
M32	M32	=SUM(M28:M31)
N32	N32	=M32*$N$24

And there are the follow names: