Advanced Excel Formula Help

[shilo322]

I have a spreadsheet that has a column of information that I need to look up in another sheet and return a formula calculation from that sheet. Basically, the Actual Work Load (AWL) is computed different based on the RU. The AWL Calculation is a formula that pertains to the SLAB sheet in the example. My limited excel knowledge says this should be easy...like an If RU from SLAB equals RU in AWL CALC then dump the formula that is in AWL CALC column B into the AWL column in the SLAB sheet and calculate it.

 

[jtakw]

Hi,

Try this:

Book3.xlsx
	A	B	C	D	E
1	RU	Mon	Units	Amt	AWL
2	AT	12	760	760	760
3	ZR	12	2000	24000	730000
4	BR	6	605	3630	3630
5	BU	12	52	624	624
6	TJ	2	52	104	8.666667
Sheet1086

Cell Formulas
Range	Formula
E2:E6	E2	=CHOOSE(MATCH(A2,CALC!A$2:A$6,0),D2,C2*365,C2*B2,C2*B2,C2/12*B2)

Book3.xlsx
	A	B
1	RU	Calculation
2	AT
3	ZR
4	BR
5	BU
6	TJ
CALC

 

[shilo322]

Thank you so much! That is so close! I need for the SLAB file to utilize the formula that is in the CALC sheet though. The solution provided doesn't account for the fact that I have 151 rows of different RU's with different calculation methods to be applied.

RU	AWL CALC
AC	=Units/12*MONTHS
AD	=Units/12*MONTHS
AG	=Units/12*MONTHS
AL	=Units/12*MONTHS
AP	=UNITS*MONTHS
AT	=Amount
AZ	=Units/12*MONTHS
BH	=Units/12*MONTHS
C1	=UNITS*MONTHS
C2	=UNITS*MONTHS
C3	=UNITS*MONTHS
C4	=UNITS*MONTHS
C5	=UNITS*MONTHS
C6	=UNITS*MONTHS
CA	=UNITS*MONTHS
CB	=Units/12*MONTHS
CC	=UNITS*MONTHS
CD	=UNITS*MONTHS
CE	=UNITS*MONTHS
CP	=UNITS*MONTHS
CR	=Units/12*MONTHS
CV	=Units/12*MONTHS
CX	=UNITS*MONTHS
DA	=UNITS*MONTHS
DF	=Units/12*MONTHS
DG	=Units/12*MONTHS
DN	=Units/12*MONTHS
DX	=Units/12*MONTHS
EC	=Units
EJ	=UNITS*MONTHS
EM	=UNITS*MONTHS
EN	=UNITS*MONTHS
EP	=UNITS*MONTHS
EQ	=UNITS*MONTHS
EV	=Units/12*MONTHS
FA	=UNITS*MONTHS
FB	=UNITS*MONTHS
FC	=UNITS*MONTHS
FD	=UNITS*MONTHS
FE	=UNITS*MONTHS
FR	=UNITS*MONTHS
FS	=UNITS*MONTHS
FT	=UNITS*MONTHS
G1	=UNITS*MONTHS
G2	=UNITS*MONTHS
G3	=UNITS*MONTHS
H1	=UNITS*MONTHS
H2	=UNITS*MONTHS
H3	=UNITS*MONTHS
H4	=UNITS*MONTHS
H5	=UNITS*MONTHS

 

[jtakw]

Thank you so much! That is so close! I need for the SLAB file to utilize the formula that is in the CALC sheet though. The solution provided doesn't account for the fact that I have 151 rows of different RU's with different calculation methods to be applied.

That's not possible, what you have in CALC are Not formulas, they are Text describing formulas.
And Yes, my solution doesn't account for your 151 rows of different RUs, because that's not what your OP shows.

In order for me to update my formula to account for All the RU calculations, I'll need the complete list, But it'll be a Very Long formula.

 

[shilo322]

That's not possible, what you have in SLAB are Not formulas, they are Text describing formulas.
And Yes, my solution doesn't account for your 151 rows of different RUs, because that's not what your OP shows.

In order for me to update my formula to account for All the RU calculations, I'll need the complete list, But it'll be a Very Long formula.

I understand what I have in the CALC sheet is a listing of text to describe the formula that needs to be applied based on the RP. I thought there would be an easier way of doing it. My apologies for not being more clear in my OP. First time posting here. Thank you for your time!

 

[jtakw]

So if you post your complete CALC list, I can figure something out.

 

[shilo322]

So if you post your complete CALC list, I can figure something out.

 

[shilo322]

Book1.xlsx
	A	B
1	RU	AWL CALC
2	AC	=Units/12*MONTHS
3	AD	=Units/12*MONTHS
4	AG	=Units/12*MONTHS
5	AL	=Units/12*MONTHS
6	AP	=UNITS*MONTHS
7	AT	=Amount
8	AZ	=Units/12*MONTHS
9	BH	=Units/12*MONTHS
10	C1	=UNITS*MONTHS
11	C2	=UNITS*MONTHS
12	C3	=UNITS*MONTHS
13	C4	=UNITS*MONTHS
14	C5	=UNITS*MONTHS
15	C6	=UNITS*MONTHS
16	CA	=UNITS*MONTHS
17	CB	=Units/12*MONTHS
18	CC	=UNITS*MONTHS
19	CD	=UNITS*MONTHS
20	CE	=UNITS*MONTHS
21	CP	=UNITS*MONTHS
22	CR	=Units/12*MONTHS
23	CV	=Units/12*MONTHS
24	CX	=UNITS*MONTHS
25	DA	=UNITS*MONTHS
26	DF	=Units/12*MONTHS
27	DG	=Units/12*MONTHS
28	DN	=Units/12*MONTHS
29	DX	=Units/12*MONTHS
30	EC	=Units
31	EJ	=UNITS*MONTHS
32	EM	=UNITS*MONTHS
33	EN	=UNITS*MONTHS
34	EP	=UNITS*MONTHS
35	EQ	=UNITS*MONTHS
36	EV	=Units/12*MONTHS
37	FA	=UNITS*MONTHS
38	FB	=UNITS*MONTHS
39	FC	=UNITS*MONTHS
40	FD	=UNITS*MONTHS
41	FE	=UNITS*MONTHS
42	FR	=UNITS*MONTHS
43	FS	=UNITS*MONTHS
44	FT	=UNITS*MONTHS
45	G1	=UNITS*MONTHS
46	G2	=UNITS*MONTHS
47	G3	=UNITS*MONTHS
48	H1	=UNITS*MONTHS
49	H2	=UNITS*MONTHS
50	H3	=UNITS*MONTHS
51	H4	=UNITS*MONTHS
52	H5	=UNITS*MONTHS
53	H6	=UNITS*MONTHS
54	H7	=UNITS*MONTHS
55	HA	=UNITS*MONTHS
56	HC	=UNITS*MONTHS
57	HD	=UNITS*MONTHS
58	HE	=UNITS*MONTHS
59	IV	=UNITS*MONTHS
60	J2	=UNITS*MONTHS
61	J3	=UNITS*MONTHS
62	J4	=UNITS*MONTHS
63	JB	=UNITS*MONTHS
64	JC	=UNITS*MONTHS
65	JD	=UNITS*MONTHS
66	KN	=Units/12*MONTHS
67	LA	=UNITS*MONTHS
68	LC	=UNITS*MONTHS
69	LD	=UNITS*MONTHS
70	LE	=UNITS*MONTHS
71	LF	=UNITS*MONTHS
72	LL	=UNITS*MONTHS
73	LM	=UNITS*MONTHS
74	LR	=UNITS*MONTHS
75	LU	=UNITS*MONTHS
76	LV	=UNITS*MONTHS
77	LW	=UNITS*MONTHS
78	LX	=UNITS*MONTHS
79	LY	=UNITS*MONTHS
80	LZ	=UNITS*MONTHS
81	ME	=Units/12*MONTHS
82	MJ	=Units/12*MONTHS
83	MS	=UNITS*MONTHS
84	MX	=UNITS*MONTHS
85	N1	=UNITS*MONTHS*30
86	N2	=UNITS*MONTHS*30
87	N5	=UNITS*MONTHS*30
88	N6	=UNITS*MONTHS*30
89	NA	=Units/12*MONTHS
90	NB	=Units/12*MONTHS
91	NL	=Units/12*MONTHS
92	NM	=Units/12*MONTHS
93	NS	=Units/12*MONTHS
94	NX	=Units/12*MONTHS
95	PR	=Units/12*MONTHS
96	PT	=Units/12*MONTHS
97	QA	=UNITS*MONTHS
98	QB	=UNITS*MONTHS
99	QC	=UNITS*MONTHS
100	QS	=UNITS*MONTHS
101	QU	=UNITS*MONTHS
102	QW	=UNITS*MONTHS
103	QX	=UNITS*MONTHS
104	RA	=Amount
105	SA	=UNITS*MONTHS
106	SD	=UNITS*MONTHS
107	SE	=UNITS*MONTHS
108	SK	=UNITS*MONTHS
109	SL	=Units/12*MONTHS
110	SM	=Units/12*MONTHS
111	SP	=Units/12*MONTHS
112	SR	=UNITS*MONTHS
113	SU	=Units/12*MONTHS
114	SX	=Units/12*MONTHS
115	SY	=UNITS*MONTHS
116	TS	=Units/12*MONTHS
117	UB	=Units/12*MONTHS
118	UE	=Units/12*MONTHS
119	UF	=Units/12*MONTHS
120	UG	=Units/12*MONTHS
121	V1	=Units/12*MONTHS
122	V2	=UNITS*MONTHS
123	V3	=Units/12*MONTHS
124	V4	=UNITS*MONTHS
125	V5	=UNITS*MONTHS
126	VA	=UNITS*MONTHS
127	VB	=UNITS*MONTHS
128	VC	=UNITS*MONTHS
129	VD	=UNITS*MONTHS
130	VE	=UNITS*MONTHS
131	VF	=UNITS*MONTHS
132	VG	=UNITS*MONTHS
133	VH	=UNITS*MONTHS
134	VJ	=UNITS*MONTHS
135	VK	=UNITS*MONTHS
136	VL	=Units/12*MONTHS
137	VM	=Units/12*MONTHS
138	VS	=Units/12*MONTHS
139	W2	=UNITS*MONTHS*30
140	W3	=UNITS*8760
141	W4	=UNITS*8760
142	W5	=UNITS*365
143	W6	=UNITS*365
144	W7	=UNITS*365
145	W8	=UNITS*365
146	WB	=UNITS*MONTHS
147	WL	=Units/12*MONTHS
148	WM	=Units/12*MONTHS
149	WS	=Units/12*MONTHS
150	WW	=Units/12*MONTHS
151	YY	=Units/12*MONTHS
AWL CALC

I hope this works!

 

[jtakw]

Thanks for posting the Table.

I've tested this to work, do some further testing yourself.

I Need you to Sort your CALC table by Column B (AWL CALC), alphabetically A to Z, Don't forget to "Expand selection", after the sort, it should look like my sample below.

Book3.xlsx
	A	B	C	D	E
1	RU	Mon	Units	Amt	AWL
2	AT	12	760	760	760
3	EC	12	2000	24000	2000
4	W5	6	605	3630	220825
5	W3	12	52	624	455520
6	AP	2	52	104	104
7	N1	6	605	3630	108900
8	AC	12	52	624	52
Sheet1086

Cell Formulas
Range	Formula
E2:E8	E2	=CHOOSE(LOOKUP(MATCH(A2,CALC!A$2:A$151,0),{1,3,4,8,10,101,106},{1,2,3,4,5,6,7}),D2,C2,C2*365,C2*8760,C2*B2,C2*B2*30,C2/12*B2)

Book3.xlsx
	A	B
1	RU	AWL CALC
2	AT	=Amount
3	RA	=Amount
4	EC	=Units
5	W5	=Units*365
6	W6	=Units*365
7	W7	=Units*365
8	W8	=Units*365
9	W3	=Units*8760
10	W4	=Units*8760
11	AP	=UNITS*MONTHS
12	C1	=UNITS*MONTHS
13	C2	=UNITS*MONTHS
14	C3	=UNITS*MONTHS
15	C4	=UNITS*MONTHS
16	C5	=UNITS*MONTHS
17	C6	=UNITS*MONTHS
18	CA	=UNITS*MONTHS
19	CC	=UNITS*MONTHS
20	CD	=UNITS*MONTHS
21	CE	=UNITS*MONTHS
22	CP	=UNITS*MONTHS
23	CX	=UNITS*MONTHS
24	DA	=UNITS*MONTHS
25	EJ	=UNITS*MONTHS
26	EM	=UNITS*MONTHS
27	EN	=UNITS*MONTHS
28	EP	=UNITS*MONTHS
29	EQ	=UNITS*MONTHS
30	FA	=UNITS*MONTHS
31	FB	=UNITS*MONTHS
32	FC	=UNITS*MONTHS
33	FD	=UNITS*MONTHS
34	FE	=UNITS*MONTHS
35	FR	=UNITS*MONTHS
36	FS	=UNITS*MONTHS
37	FT	=UNITS*MONTHS
38	G1	=UNITS*MONTHS
39	G2	=UNITS*MONTHS
40	G3	=UNITS*MONTHS
41	H1	=UNITS*MONTHS
42	H2	=UNITS*MONTHS
43	H3	=UNITS*MONTHS
44	H4	=UNITS*MONTHS
45	H5	=UNITS*MONTHS
46	H6	=UNITS*MONTHS
47	H7	=UNITS*MONTHS
48	HA	=UNITS*MONTHS
49	HC	=UNITS*MONTHS
50	HD	=UNITS*MONTHS
51	HE	=UNITS*MONTHS
52	IV	=UNITS*MONTHS
53	J2	=UNITS*MONTHS
54	J3	=UNITS*MONTHS
55	J4	=UNITS*MONTHS
56	JB	=UNITS*MONTHS
57	JC	=UNITS*MONTHS
58	JD	=UNITS*MONTHS
59	LA	=UNITS*MONTHS
60	LC	=UNITS*MONTHS
61	LD	=UNITS*MONTHS
62	LE	=UNITS*MONTHS
63	LF	=UNITS*MONTHS
64	LL	=UNITS*MONTHS
65	LM	=UNITS*MONTHS
66	LR	=UNITS*MONTHS
67	LU	=UNITS*MONTHS
68	LV	=UNITS*MONTHS
69	LW	=Units*MONTHS
70	LX	=Units*MONTHS
71	LY	=Units*MONTHS
72	LZ	=Units*MONTHS
73	MS	=Units*MONTHS
74	MX	=Units*MONTHS
75	QA	=Units*MONTHS
76	QB	=Units*MONTHS
77	QC	=Units*MONTHS
78	QS	=Units*MONTHS
79	QU	=Units*MONTHS
80	QW	=Units*MONTHS
81	QX	=Units*MONTHS
82	SA	=Units*MONTHS
83	SD	=Units*MONTHS
84	SE	=Units*MONTHS
85	SK	=Units*MONTHS
86	SR	=Units*MONTHS
87	SY	=Units*MONTHS
88	V2	=Units*MONTHS
89	V4	=Units*MONTHS
90	V5	=Units*MONTHS
91	VA	=Units*MONTHS
92	VB	=Units*MONTHS
93	VC	=Units*MONTHS
94	VD	=Units*MONTHS
95	VE	=Units*MONTHS
96	VF	=Units*MONTHS
97	VG	=Units*MONTHS
98	VH	=Units*MONTHS
99	VJ	=Units*MONTHS
100	VK	=Units*MONTHS
101	WB	=Units*MONTHS
102	N1	=Units*MONTHS*30
103	N2	=Units*MONTHS*30
104	N5	=Units*MONTHS*30
105	N6	=Units*MONTHS*30
106	W2	=Units*MONTHS*30
107	AC	=Units/12*MONTHS
108	AD	=Units/12*MONTHS
109	AG	=Units/12*MONTHS
110	AL	=Units/12*MONTHS
111	AZ	=Units/12*MONTHS
112	BH	=Units/12*MONTHS
113	CB	=Units/12*MONTHS
114	CR	=Units/12*MONTHS
115	CV	=Units/12*MONTHS
116	DF	=Units/12*MONTHS
117	DG	=Units/12*MONTHS
118	DN	=Units/12*MONTHS
119	DX	=Units/12*MONTHS
120	EV	=Units/12*MONTHS
121	KN	=Units/12*MONTHS
122	ME	=Units/12*MONTHS
123	MJ	=Units/12*MONTHS
124	NA	=Units/12*MONTHS
125	NB	=Units/12*MONTHS
126	NL	=Units/12*MONTHS
127	NM	=Units/12*MONTHS
128	NS	=Units/12*MONTHS
129	NX	=Units/12*MONTHS
130	PR	=Units/12*MONTHS
131	PT	=Units/12*MONTHS
132	SL	=Units/12*MONTHS
133	SM	=Units/12*MONTHS
134	SP	=Units/12*MONTHS
135	SU	=Units/12*MONTHS
136	SX	=Units/12*MONTHS
137	TS	=Units/12*MONTHS
138	UB	=Units/12*MONTHS
139	UE	=Units/12*MONTHS
140	UF	=Units/12*MONTHS
141	UG	=Units/12*MONTHS
142	V1	=Units/12*MONTHS
143	V3	=Units/12*MONTHS
144	VL	=Units/12*MONTHS
145	VM	=Units/12*MONTHS
146	VS	=Units/12*MONTHS
147	WL	=Units/12*MONTHS
148	WM	=Units/12*MONTHS
149	WS	=Units/12*MONTHS
150	WW	=Units/12*MONTHS
151	YY	=Units/12*MONTHS
CALC

 

[shilo322]

Thanks for posting the Table.

I've tested this to work, do some further testing yourself.

I Need you to Sort your CALC table by Column B (AWL CALC), alphabetically A to Z, Don't forget to "Expand selection", after the sort, it should look like my sample below.

Book3.xlsx
	A	B	C	D	E
1	RU	Mon	Units	Amt	AWL
2	AT	12	760	760	760
3	EC	12	2000	24000	2000
4	W5	6	605	3630	220825
5	W3	12	52	624	455520
6	AP	2	52	104	104
7	N1	6	605	3630	108900
8	AC	12	52	624	52
Sheet1086

Cell Formulas
Range	Formula
E2:E8	E2	=CHOOSE(LOOKUP(MATCH(A2,CALC!A$2:A$151,0),{1,3,4,8,10,101,106},{1,2,3,4,5,6,7}),D2,C2,C2*365,C2*8760,C2*B2,C2*B2*30,C2/12*B2)

Book3.xlsx
	A	B
1	RU	AWL CALC
2	AT	=Amount
3	RA	=Amount
4	EC	=Units
5	W5	=Units*365
6	W6	=Units*365
7	W7	=Units*365
8	W8	=Units*365
9	W3	=Units*8760
10	W4	=Units*8760
11	AP	=UNITS*MONTHS
12	C1	=UNITS*MONTHS
13	C2	=UNITS*MONTHS
14	C3	=UNITS*MONTHS
15	C4	=UNITS*MONTHS
16	C5	=UNITS*MONTHS
17	C6	=UNITS*MONTHS
18	CA	=UNITS*MONTHS
19	CC	=UNITS*MONTHS
20	CD	=UNITS*MONTHS
21	CE	=UNITS*MONTHS
22	CP	=UNITS*MONTHS
23	CX	=UNITS*MONTHS
24	DA	=UNITS*MONTHS
25	EJ	=UNITS*MONTHS
26	EM	=UNITS*MONTHS
27	EN	=UNITS*MONTHS
28	EP	=UNITS*MONTHS
29	EQ	=UNITS*MONTHS
30	FA	=UNITS*MONTHS
31	FB	=UNITS*MONTHS
32	FC	=UNITS*MONTHS
33	FD	=UNITS*MONTHS
34	FE	=UNITS*MONTHS
35	FR	=UNITS*MONTHS
36	FS	=UNITS*MONTHS
37	FT	=UNITS*MONTHS
38	G1	=UNITS*MONTHS
39	G2	=UNITS*MONTHS
40	G3	=UNITS*MONTHS
41	H1	=UNITS*MONTHS
42	H2	=UNITS*MONTHS
43	H3	=UNITS*MONTHS
44	H4	=UNITS*MONTHS
45	H5	=UNITS*MONTHS
46	H6	=UNITS*MONTHS
47	H7	=UNITS*MONTHS
48	HA	=UNITS*MONTHS
49	HC	=UNITS*MONTHS
50	HD	=UNITS*MONTHS
51	HE	=UNITS*MONTHS
52	IV	=UNITS*MONTHS
53	J2	=UNITS*MONTHS
54	J3	=UNITS*MONTHS
55	J4	=UNITS*MONTHS
56	JB	=UNITS*MONTHS
57	JC	=UNITS*MONTHS
58	JD	=UNITS*MONTHS
59	LA	=UNITS*MONTHS
60	LC	=UNITS*MONTHS
61	LD	=UNITS*MONTHS
62	LE	=UNITS*MONTHS
63	LF	=UNITS*MONTHS
64	LL	=UNITS*MONTHS
65	LM	=UNITS*MONTHS
66	LR	=UNITS*MONTHS
67	LU	=UNITS*MONTHS
68	LV	=UNITS*MONTHS
69	LW	=Units*MONTHS
70	LX	=Units*MONTHS
71	LY	=Units*MONTHS
72	LZ	=Units*MONTHS
73	MS	=Units*MONTHS
74	MX	=Units*MONTHS
75	QA	=Units*MONTHS
76	QB	=Units*MONTHS
77	QC	=Units*MONTHS
78	QS	=Units*MONTHS
79	QU	=Units*MONTHS
80	QW	=Units*MONTHS
81	QX	=Units*MONTHS
82	SA	=Units*MONTHS
83	SD	=Units*MONTHS
84	SE	=Units*MONTHS
85	SK	=Units*MONTHS
86	SR	=Units*MONTHS
87	SY	=Units*MONTHS
88	V2	=Units*MONTHS
89	V4	=Units*MONTHS
90	V5	=Units*MONTHS
91	VA	=Units*MONTHS
92	VB	=Units*MONTHS
93	VC	=Units*MONTHS
94	VD	=Units*MONTHS
95	VE	=Units*MONTHS
96	VF	=Units*MONTHS
97	VG	=Units*MONTHS
98	VH	=Units*MONTHS
99	VJ	=Units*MONTHS
100	VK	=Units*MONTHS
101	WB	=Units*MONTHS
102	N1	=Units*MONTHS*30
103	N2	=Units*MONTHS*30
104	N5	=Units*MONTHS*30
105	N6	=Units*MONTHS*30
106	W2	=Units*MONTHS*30
107	AC	=Units/12*MONTHS
108	AD	=Units/12*MONTHS
109	AG	=Units/12*MONTHS
110	AL	=Units/12*MONTHS
111	AZ	=Units/12*MONTHS
112	BH	=Units/12*MONTHS
113	CB	=Units/12*MONTHS
114	CR	=Units/12*MONTHS
115	CV	=Units/12*MONTHS
116	DF	=Units/12*MONTHS
117	DG	=Units/12*MONTHS
118	DN	=Units/12*MONTHS
119	DX	=Units/12*MONTHS
120	EV	=Units/12*MONTHS
121	KN	=Units/12*MONTHS
122	ME	=Units/12*MONTHS
123	MJ	=Units/12*MONTHS
124	NA	=Units/12*MONTHS
125	NB	=Units/12*MONTHS
126	NL	=Units/12*MONTHS
127	NM	=Units/12*MONTHS
128	NS	=Units/12*MONTHS
129	NX	=Units/12*MONTHS
130	PR	=Units/12*MONTHS
131	PT	=Units/12*MONTHS
132	SL	=Units/12*MONTHS
133	SM	=Units/12*MONTHS
134	SP	=Units/12*MONTHS
135	SU	=Units/12*MONTHS
136	SX	=Units/12*MONTHS
137	TS	=Units/12*MONTHS
138	UB	=Units/12*MONTHS
139	UE	=Units/12*MONTHS
140	UF	=Units/12*MONTHS
141	UG	=Units/12*MONTHS
142	V1	=Units/12*MONTHS
143	V3	=Units/12*MONTHS
144	VL	=Units/12*MONTHS
145	VM	=Units/12*MONTHS
146	VS	=Units/12*MONTHS
147	WL	=Units/12*MONTHS
148	WM	=Units/12*MONTHS
149	WS	=Units/12*MONTHS
150	WW	=Units/12*MONTHS
151	YY	=Units/12*MONTHS
CALC

YOU ARE AMAZING SIR!!!!! Thank you from the depths of my heart for solving this riddle! I love tricky formulas but this one definitely had me stumped! At least I learned some new tricks along the way. Thank you again!