Formula not working in google sheets

[Ludus2015]

I just uploaded a fully working excel spreadsheet into google sheets, most of the formulas work fine, except for the most important one. They all work flawlessly on the excel file itself, and they all work fine when I uploaded it into google sheets. Except for the one. Is there some sort of a context issue in the formula below that would not allow google to see it properly? This formula works fine in excel on the desktop but when the spreadsheet got uploaded it just return a ERROR message on the Cell its intended for.

=AGGREGATE(14,6,ROW(TBLFitnesstests[Name])-1/(TBLFitnesstests[Name]=$A$3),$S5)

Ultimately all I'm trying to do is select any name and have their data from the database populate on the Results Chart Field spreadsheet.

 

[Michael M]

I don't believe Google sheets has the AGGREGATE function capability.
You might try SUBTOTAL instead. It's a similar function.

SUBTOTAL function - Docs Editors Help

 

[Ludus2015]

Results chart.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1																						3
2
3	Riley Colborne																				Name	Riley Colborne		Date	15/12/2020	Weight(kg)	80	Heart rate (bpm)	0			Name	Athletic score
4		Date			BW	Resting Heart Rate														row			Result		Ratio	Score (M)		Score (F)				Riley Colborne	39
5	EXERCISE	15/12/2020			80	0		MOBILITY ASSESSMENT			R	L	RATIO	COMMENTS					1	188		SQUAT	0		0.00	0		0
6	LOWER BODY STRENGTH	R	L	RATIO	SCORE	COMMENTS		KNEE TO WALL			0	0	#DIV/0!									DEADLIFT	140		1.75	7		9
7	BACK SQUAT (kg)	0		0.00	0			BENT KNEE HIP FELXION			0	0	#DIV/0!									BENCH PRESS	68		0.85	4		8
8	DEADLIFT (kg)	140		1.75	7			STRAIGHT LEG RAISE			0	0	#DIV/0!									CHIN UP	92.5		1.16	4		7
9	RFESS (kg)	56		0.70	4			HIP INTERNAL ROTATION			0	0	#DIV/0!									RFESS	56		0.70	4		7
10								HIP EXTERNAL ROTATION			0	0	#DIV/0!									VERTICAL JUMP	86			11		12
11	UPPER BODY STRENGTH	R	L	RATIO	SCORE	COMMENTS		HIP EXTENSION			0	0										SPRINT	2.85			9		0
12	BENCH PRESS (kg)	68		0.85	4			THORACIC ROTATION			0	0	#DIV/0!									PRO-AGILITY	0			0		0
13	CHIN UP (kg)	92.5		1.16	4			ACTIVE IR/ER ROM (shoulder)			0	0	#DIV/0!									SLED	0			0		0
14								WALL SHOULDER FLEXION			0	0	#DIV/0!									YOYO	0			0		0
15	LOWER BODY POWER	R	L	RATIO	SCORE	COMMENTS																				39		43
16	VERTICAL JUMP (cm)	86			11																	ATHLETIC SCORE				39%		43%
17	VERTICAL NON-CMJ (cm)	0.00	0.00	#DIV/0!
18	VERTICAL CMJ (cm)	0.00	0.00	#DIV/0!																			R	L
19	VERTICAL 10-5 (RSI) (m.s)	0.00	0.00	#DIV/0!																		VERTICAL NON-CMJ (cm)	0	0
20	LANDING	0.00	0.00	#DIV/0!																		VERTICAL CMJ (cm)	0	0
21	HORIZONTAL NON-CMJ (m)	0.00	0.00	#DIV/0!																		VERTICAL 10-5 (RSI) (m.s)	0	0
22	HORIZONTAL CMJ (m)	0.00	0.00	#DIV/0!																		LANDING	0	0
23	HORIZONTAL TRIPLE JUMP (m)	0.00	0.00	#DIV/0!																		HORIZONTAL NON-CMJ (m)	0	0
24																						HORIZONTAL CMJ (m)	0	0
25	SPEED/AGILITY			RATIO	SCORE	COMMENTS																HORIZONTAL TRIPLE JUMP (m)	0	0
26	20M SPRINT (sec)	2.85			9																	40M SPRINT (sec)	4.87
27	40M SPRINT (sec)	4.87																				SIDE PLANK (sec)	0	0
28	PRO AGILITY (sec)	0.000			0																	DEADBUG HOLD (sec)	0
29																						SIDE PLANK LEG RAISE (sec)	0	0
30	ENDURANCE / REPEAT POWER	R	L	RATIO	SCORE	COMMENTS																S/L HIP THRUSTER	0	0
31	YO-YO	0			0																	S/L SQUAT TO BOX	0	0
32	SLED TEST	0			0																	NORDIC HAMSTRING (deg)	0
33																						S/L CALF RAISE	0	0
34	CORE	R	L	RATIO	SCORE	COMMENTS																SIDE LYING EXTERNAL ROTATION (kg)	0	0
35	SIDE PLANK (sec)	0	0	#DIV/0!																		ONE ARM PLANK	0	0
36	DEADBUG HOLD (sec)	0																				INVERTED ROW HOLD (sec)	0
37																						KNEE TO WALL	0	0
38	LOWER BODY CAPACITY	R	L	RATIO	SCORE	COMMENTS																BENT KNEE HIP FELXION	0	0
39	SIDE PLANK LEG RAISE (sec)	0	0	#DIV/0!																		STRAIGHT LEG RAISE	0	0
40	S/L HIP THRUSTER	0	0	#DIV/0!																		HIP INTERNAL ROTATION	0	0
41	S/L SQUAT TO BOX	0	0	#DIV/0!																		HIP EXTERNAL ROTATION	0	0
42	NORDIC HAMSTRING (deg)	0																				HIP EXTENSION	0	0
43	S/L CALF RAISE	0	0	#DIV/0!				GOALS														THORACIC ROTATION	0	0
44	UPPER BODY CAPACITY	R	L	RATIO	SCORE	COMMENTS																ACTIVE IR/ER ROM (shoulder)	0	0
45	SIDE LYING EXTERNAL ROTATION (kg)	0	0	#DIV/0!																		WALL SHOULDER FLEXION	0	0
46	ONE ARM PLANK	0	0	#DIV/0!
47	INVERTED ROW HOLD (sec)	0
48
49	ATHLETIC SCORE / 100	39
50
Results Chart Field

Cell Formulas
Range	Formula
V3	V3	=A3
Y3	Y3	=INDEX(TBLFitnesstests[Date],$T$5)
AA3	AA3	=INDEX(TBLFitnesstests[Body Weight (kg)],$T$5)
AC3	AC3	=INDEX(TBLFitnesstests[Resting Heart Rate],$T$5)
AF4	AF4	=A3
AG4	AG4	=B49
B5,F5	B5	=Y3
E5	E5	=AA3
T5	T5	=AGGREGATE(14,6,ROW(TBLFitnesstests[Name])-1/(TBLFitnesstests[Name]=$A$3),$S5)
K6:L14	K6	=W37
M6:M10,D45:D46,D43,D39:D41,D35,D17:D23,M12:M14	M6	=(K6-L6)/L6
B7:B8,D7:D8	B7	=W5
B9,D9	B9	=W9
Y5	Y5	=W5/AA3
Y6	Y6	=W6/AA3
Y7	Y7	=W7/AA3
Y8	Y8	=W8/AA3
Y9	Y9	=W9/AA3
B12:B13,D12:D13	B12	=W7
W5	W5	=INDEX(TBLFitnesstests[Squat],$T$5)
W6	W6	=INDEX(TBLFitnesstests[Deadlift],$T$5)
W7	W7	=INDEX(TBLFitnesstests[Bench Press],$T$5)
W8	W8	=INDEX(TBLFitnesstests[Chin Up],$T$5)
W9	W9	=INDEX(TBLFitnesstests[RFESS],$T$5)
W10	W10	=INDEX(TBLFitnesstests[Vertical Jump],$T$5)
W11	W11	=INDEX(TBLFitnesstests[20m Sprint],$T$5)
W12	W12	=INDEX(TBLFitnesstests[ProAgility],$T$5)
W13	W13	=INDEX(TBLFitnesstests[Sled],$T$5)
W14	W14	=INDEX(TBLFitnesstests[YoYo],$T$5)
B16	B16	=W10
Z5	Z5	=IFERROR(VLOOKUP(Y5,STANDARDS!$A$3:$K$14,11,TRUE),"0")
Z6	Z6	=IFERROR(VLOOKUP(Y6,STANDARDS!$B$3:$K$14,10,TRUE),"0")
Z7	Z7	=IFERROR(VLOOKUP(Y7,STANDARDS!$C$3:$K$14,9,TRUE),"0")
Z8	Z8	=IFERROR(VLOOKUP(Y8,STANDARDS!$D$3:$K$14,8,TRUE),"0")
Z9	Z9	=IFERROR(VLOOKUP(Y9,STANDARDS!$E$3:$K$14,7,TRUE),"0")
Z10	Z10	=IFERROR(VLOOKUP(W10,STANDARDS!$F$3:$K$14,6,TRUE),"0")
Z11	Z11	=IFERROR(VLOOKUP(W11,STANDARDS!$G$15:$I$27,3,TRUE),"0")
Z12	Z12	=IFERROR(VLOOKUP(W12,STANDARDS!$H$15:$I$27,2,TRUE),"0")
Z13	Z13	=IFERROR(VLOOKUP(W13,STANDARDS!$I$3:$K$14,3,TRUE),"0")
Z14	Z14	=IFERROR(VLOOKUP(W14,STANDARDS!$J$3:$K$14,2,TRUE),"0")
Z15,AB15	Z15	=SUM(Z5:Z14)
Z16,AB16	Z16	=SUM(Z5:Z14)/100
AB5	AB5	=IFERROR(VLOOKUP(Y5,STANDARDS!$M$3:$W$14,11,TRUE),"0")
AB6	AB6	=IFERROR(VLOOKUP(Y6,STANDARDS!$N$3:$W$14,10,TRUE),"0")
AB7	AB7	=IFERROR(VLOOKUP(Y7,STANDARDS!$O$3:$W$14,9,TRUE),"0")
AB8	AB8	=IFERROR(VLOOKUP(Y8,STANDARDS!$P$3:$W$14,8,TRUE),"0")
AB9	AB9	=IFERROR(VLOOKUP(Y9,STANDARDS!$Q$3:$W$14,7,TRUE),"0")
AB10	AB10	=IFERROR(VLOOKUP(W10,STANDARDS!$R$3:$W$14,6,TRUE),"0")
AB11	AB11	=IFERROR(VLOOKUP(W11,STANDARDS!S15:U27,3,TRUE),"0")
AB12	AB12	=IFERROR(VLOOKUP(W12,STANDARDS!T15:U27,2,TRUE),"0")
AB13	AB13	=IFERROR(VLOOKUP(W13,STANDARDS!$U$3:$W$14,3,TRUE),"0")
AB14	AB14	=IFERROR(VLOOKUP(W14,STANDARDS!$V$3:$W$14,2,TRUE),"0")
B17:C23	B17	=W19
W19	W19	=INDEX(TBLFitnesstests[VNCMR],$T$5)
X19	X19	=INDEX(TBLFitnesstests[VNCML],$T$5)
W20	W20	=INDEX(TBLFitnesstests[VCMJR],$T$5)
X20	X20	=INDEX(TBLFitnesstests[VCMJL],$T$5)
W21	W21	=INDEX(TBLFitnesstests[Ankle RSI R],$T$5)
X21	X21	=INDEX(TBLFitnesstests[Ankle RSI L],$T$5)
W22	W22	=INDEX(TBLFitnesstests[Weighted S/L Landing R],$T$5)
X22	X22	=INDEX(TBLFitnesstests[Weighted S/L Landing L],$T$5)
W23	W23	=INDEX(TBLFitnesstests[HNCMJR],$T$5)
X23	X23	=INDEX(TBLFitnesstests[HNCMJL],$T$5)
W24	W24	=INDEX(TBLFitnesstests[HCMJR],$T$5)
X24	X24	=INDEX(TBLFitnesstests[HCMJL],$T$5)
W25	W25	=INDEX(TBLFitnesstests[HRJR],$T$5)
X25	X25	=INDEX(TBLFitnesstests[HRJL],$T$5)
W26	W26	=INDEX(TBLFitnesstests[40m Sprint],$T$5)
W27	W27	=INDEX(TBLFitnesstests[Side Plank R],$T$5)
X27	X27	=INDEX(TBLFitnesstests[Side Plank L],$T$5)
W28	W28	=INDEX(TBLFitnesstests[Deadbug Hold],$T$5)
W29	W29	=INDEX(TBLFitnesstests[Side Plank Leg Raise R],$T$5)
X29	X29	=INDEX(TBLFitnesstests[Side Plank Leg Raise L],$T$5)
W30	W30	=INDEX(TBLFitnesstests[S/L Hip Thrust R],$T$5)
X30	X30	=INDEX(TBLFitnesstests[S/L Hip Thrust L],$T$5)
W31	W31	=INDEX(TBLFitnesstests[S/L Squat to Box R],$T$5)
X31	X31	=INDEX(TBLFitnesstests[S/L Squat to Box L],$T$5)
W32	W32	=INDEX(TBLFitnesstests[Nordic Hamstring Fall],$T$5)
W33	W33	=INDEX(TBLFitnesstests[S/L Calf Raise R],$T$5)
X33	X33	=INDEX(TBLFitnesstests[S/L Calf Raise L],$T$5)
W34	W34	=INDEX(TBLFitnesstests[Dumbbell External Rotation R],$T$5)
X34	X34	=INDEX(TBLFitnesstests[Dumbbell External Rotation L],$T$5)
W35	W35	=INDEX(TBLFitnesstests[One Arm Plank R],$T$5)
X35	X35	=INDEX(TBLFitnesstests[One Arm Plank L],$T$5)
W36	W36	=INDEX(TBLFitnesstests[Inverted Row],$T$5)
W37	W37	=INDEX(TBLFitnesstests[Knee to wall R],$T$5)
X37	X37	=INDEX(TBLFitnesstests[Knee to wall L],$T$5)
W38	W38	=INDEX(TBLFitnesstests[Bent Knee Hip Flexion R],$T$5)
X38	X38	=INDEX(TBLFitnesstests[Bent Knee Hip Flexion L],$T$5)
W39	W39	=INDEX(TBLFitnesstests[Straight Leg Raise R],$T$5)
X39	X39	=INDEX(TBLFitnesstests[Straight Leg Raise L],$T$5)
W40	W40	=INDEX(TBLFitnesstests[Hip Internal Rotation R],$T$5)
X40	X40	=INDEX(TBLFitnesstests[Hip Internal Rotation L],$T$5)
W41	W41	=INDEX(TBLFitnesstests[Hip External Rotation R],$T$5)
X41	X41	=INDEX(TBLFitnesstests[Hip External Rotation L],$T$5)
W42	W42	=INDEX(TBLFitnesstests[Hip Extension R],$T$5)
X42	X42	=INDEX(TBLFitnesstests[Hip Extension L],$T$5)
W43	W43	=INDEX(TBLFitnesstests[Thoracic Rotation R],$T$5)
X43	X43	=INDEX(TBLFitnesstests[Thoracic Rotation L],$T$5)
W44	W44	=INDEX(TBLFitnesstests[Active Internal/External Rotation R],$T$5)
X44	X44	=INDEX(TBLFitnesstests[Active Internal/External Rotation L],$T$5)
W45	W45	=INDEX(TBLFitnesstests[Wall Shoulder Flexion R],$T$5)
X45	X45	=INDEX(TBLFitnesstests[Wall Shoulder Flexion L],$T$5)
B26	B26	=W11
B27	B27	=W26
B28	B28	=W12
B31	B31	=W14
B32	B32	=W13
B35:C35,B36	B35	=W27
B39:C41,B43:C43,B42	B39	=W29
B45:C46,B47	B45	=W34
B49	B49	=SUM(E7,E8,E9,E12,E13,E16,E26,E28,E31,E32)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B47:C47	Other Type	Icon set		NO
B46:C46	Other Type	Icon set		NO
B45:C45	Other Type	Icon set		NO
K6:L6	Other Type	Icon set		NO
K13:L13	Other Type	Icon set		NO
K12:L12	Other Type	Icon set		NO
K9:L10	Other Type	Icon set		NO
K8:L8	Other Type	Icon set		NO
K7:L7	Other Type	Icon set		NO
K14:L14	Other Type	Icon set		NO
B43:C43	Other Type	Icon set		NO
B41:C41	Other Type	Icon set		NO
B40:C40	Other Type	Icon set		NO
B39:C39	Other Type	Icon set		NO
B42:C42	Other Type	Icon set		NO
B36:C36	Other Type	Icon set		NO
B35:C35	Other Type	Icon set		NO
M6:M10,M12:M14	Cell Value	<0.1	text	NO
M6:M10,M12:M14	Cell Value	>0.1	text	NO
D17:D23,D35,D39:D41,D43,D45:D46	Cell Value	<0.1	text	NO
D35,D39:D41,D43,D45:D46	Cell Value	>0.1	text	NO
D18:D23	Cell Value	>0.1	text	NO
D17	Cell Value	>0.1	text	NO
AA20:AA29	Other Type	Color scale		NO
AC5:AC14	Other Type	Color scale		NO
E7:E9,E12:E13,E16:E23,E26:E28,E31:E32,E35:E36,E39:E43,E45:E47	Other Type	Color scale		NO

Cells with Data Validation
Cell	Allow	Criteria
A3:P3	List	=Names!$B$3:$B$202

 

[Ludus2015]

Still getting an error message

 

[Ludus2015]

Loom - Results Chart

Use Loom to record quick videos of your screen and cam. Explain anything clearly and easily – and skip the meeting. An essential tool for hybrid workplaces.

www.loom.com

 

[Fluff]

I didn't think that Sheets had tables. Are you sure all the formula that use structured references are working?

 

[Ludus2015]

I didn't think that Sheets had tables. Are you sure all the formula that use structured references are working?

The other formulas are working

 

[Fluff]

Are they still using structured references, or have they changed to ranges?