How to fix #VALUE! error in a Hyperlink() link

[psycoperl]

I am trying to figure out how to fix the #VALUE! error that occurred in my spreadsheet. The formula worked until I changed the value of the cell H3 by adding text to the string that was there.

Any suggestions would be appreciated.

SCFE_SP21_MCR_ACCOUNTANCY_ACC_GRAD.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3							File ID:	SCFE_SP21_MCR_ACCOUNTANCY_ACC_GRAD
4
5			Student Course and Faculty Evaluation				Stats:	Sections To Be Reviewed		Crosslistings To Be Reviewed		Instructors Missing	Sections Reviewed Missing Signature
6			Master Course Roster - Spring 2021					16		16		1	0
Instructions

Cell Formulas
Range	Formula
H6	H6	=COUNTIFS('Course Info'!Q:Q,"[-] NEEDS RESPONSE",'Course Info'!A:A,"<>")
J6	J6	=COUNTIFS('Course Info'!A:A,"<>",'Course Info'!S:S,"[-] NEEDS RESPONSE",'Course Info'!Q:Q,'Preset Values'!F2)+COUNTIFS('Course Info'!A:A,"<>",'Course Info'!S:S,"[-] NEEDS RESPONSE",'Course Info'!Q:Q,'Preset Values'!F3)+COUNTIFS('Course Info'!A:A,"<>",'Course Info'!S:S,"[-] NEEDS RESPONSE",'Course Info'!Q:Q,'Preset Values'!F4)+COUNTIFS('Course Info'!A:A,"<>",'Course Info'!S:S,"[-] NEEDS RESPONSE",'Course Info'!Q:Q,'Preset Values'!F5)+COUNTIFS('Course Info'!A:A,"<>",'Course Info'!S:S,"[-] NEEDS RESPONSE",'Course Info'!Q:Q,'Preset Values'!F6)+COUNTIFS('Course Info'!A:A,"<>",'Course Info'!S:S,"[-] NEEDS RESPONSE",'Course Info'!Q:Q,'Preset Values'!F7)
L6	L6	=COUNTIFS('Course Info'!A:A,"<>",'Course Info'!K:K,"00000000",'Course Info'!Q:Q,'Preset Values'!F2)+COUNTIFS('Course Info'!A:A,"<>",'Course Info'!K:K,"00000000",'Course Info'!Q:Q,'Preset Values'!F3)+COUNTIFS('Course Info'!A:A,"<>",'Course Info'!K:K,"00000000",'Course Info'!Q:Q,'Preset Values'!F4)+COUNTIFS('Course Info'!A:A,"<>",'Course Info'!K:K,"00000000",'Course Info'!Q:Q,'Preset Values'!F5)+COUNTIFS('Course Info'!A:A,"<>",'Course Info'!K:K,"00000000",'Course Info'!Q:Q,'Preset Values'!F6)+COUNTIFS('Course Info'!A:A,"<>",'Course Info'!K:K,"00000000",'Course Info'!Q:Q,'Preset Values'!F7)
M6	M6	=COUNTIFS('Course Info'!$A:$A,"<>",'Course Info'!$X:$X,"",'Course Info'!Q:Q,"<>[-] NEEDS RESPONSE")

Named Ranges
Name	Refers To	Cells
'Course Info'!_FilterDatabase	='Course Info'!$A$2:$Z$10098	H6:I6, L6:M6
_RecordStatus	='Preset Values'!$F$2:$F$13	L6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M6	Cell Value	<1	text	NO
M6	Cell Value	between 1 and 10	text	NO
M6	Cell Value	>10	text	NO
L6	Cell Value	<1	text	NO
L6	Cell Value	between 1 and 10	text	NO
L6	Cell Value	>10	text	NO
J6:K6	Cell Value	<1	text	NO
J6:K6	Cell Value	between 1 and 10	text	NO
J6:K6	Cell Value	>10	text	NO
H6	Cell Value	<1	text	NO
H6	Cell Value	between 1 and 10	text	NO
H6	Cell Value	>10	text	NO

--

SCFE_SP21_MCR_ACCOUNTANCY_ACC_GRAD.xlsx
	I	J	K	L	M	N	O
28
29		#VALUE!
30
31
32
Instructions

Cell Formulas
Range	Formula
J29	J29	=HYPERLINK(CONCATENATE("mailto:FE@MYU.edu?Subject=SCFE ",C6," [",H3,"]&body=",C5, " ", C6, " file ID ", H3, " is ready for processing."),"Click here to notify the OTE that the file is completed.")

 

[Fluff]

That works for me, try using the Evaluate Formula button on the formula tab to see where it fails.

 

[psycoperl]

This may be a simple question... I do not see an Evaluate Formula button on my ribbon

I have

1. Insert Function
2. Auto Sum
3. Recently Used
4. Financial
5. Logical
6. Text
7. Date & Time
8. Lookup & Reference
9. Math & Trig
10. More Functions
11. Define Name
12. Create From Selection
13. Trace Precidents
14. Trace Dependents
15. Remove Arrows
16. Show Formulas
17. Error Checking
18. Watch Window
19. Calculation Options
20. Calculate Now
21. Calculate Sheet

 

[Fluff]

I do not see an Evaluate Formula button on my ribbon

No you won't because your on a Mac, but you will if you open the file in windows.