IF formula stops referencing columns after a certain point unless I manually type

pzupan

I have a survey going to to people with 56 compliance rules. When the survey answers as "out of compliance" I have a rules column for each rule change that to a "1". Another column I've made have multiple selection options with the rule number and compliance issue. So Ideally when someone answers "out of compliance for rule 581-022-0106" as well as other coumns, in the single drop down column each rule number and reference will appear as "581-022-0106 State Standards for 2021-22" for example.

The following formula works to achieve this:

=IF([Rule 581-022-0106]@row = "1", "581-022-0106 State Standards for 2021-22") + CHAR(10) + IF([Rule 581-022-2000]@row = "1", "581-022-2000 Diploma Requirements") + CHAR(10)

If someone selects 2000 but not 0106, only 2000 will appear in the summary column. If they select both, both appear. Exactly what I need.

So I made a word doc, wrote out the entire function and it doesn't work! The format is exactly the same, only the column name and output changed. When I paste the function, all the rows stop referencing, and I get an unparceable error. I have to delicately type out function for the refences to stay. Am I doing something wrong? Is there a way to make the rows reference from a plaintext formula?

Paul Newcome

Try using Notepad instead of word. I would be willing to bet that if you looked at the Word doc, all the quotes will be slanted. Those slanted quotes are called "Smart Quotes" which (ironically enough) Smartsheet does not recognize as valid characters.

If you notice, the quotes typed here, directly in Smartsheet, or in Notepad are vertical. These are the types of quotes you need.

pzupan

cell formula.txt

That's frustrating. However that didn't fix it. I pasted into notepad then had it replace all the quotes so they all look like vertical ones, then pasted that into Smartsheet. Still did not recognize the rows and said unparseable

Andrée Starå

Hi @pzupan

I hope you're well and safe!

The issue is probably that you're over the character limit (4000 characters, including spaces)

Try changing the column name(s), so it takes fewer characters.

Did that work?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

pzupan

It did not. Even if I only paste half the formula from notepad, Smartsheet still does not recognize the rows and makes it unparceable.

Andrée Starå

@pzupan

Can you paste the formula here in text form?

Paul Newcome

Here is the formula from the notepad doc (5,716 characters including spaces). Aside from it being entirely too long, it looks like you are missing an IF to start the bold portion.

=IF([Rule 581-022-0106]@row = "1", "581-022-0106 State Standards for 2021-22") + CHAR(10) + IF([Rule 581-022-2000]@row = "1", "581-022-2000 Diploma Requirements") + CHAR(10) + IF([Rule 581-022-2005]@row = "1", "581-022-2005 Veterans Diploma") + CHAR(10) + IF([Rule 581-022-2010]@row = "1", "581-022-2010 Modified Diploma") + CHAR(10) + IF([Rule 581-022-2015]@row = "1", "581-022-2015 Extended Diploma") + CHAR(10) + IF([Rule 581-022-2020]@row = "1", "581-022-2020 Alternative Certificate") + CHAR(10) + IF([Rule 581-022-2025]@row = "1", "581-022-2025 Credit Options") + CHAR(10) + IF([Rule 581-022-2030]@row = "1", "581-022-2030 District Curriculum") + CHAR(10) + IF([Rule 581-022-2045]@row = "1", "581-022-2045 Prevention Education in Drugs and Alcohol") + CHAR(10) + IF([Rule 581-022-2050]@row = "1", "581-022-2050 Human Sexuality Education") + CHAR(10) + IF([Rule 581-022-2055]@row = "1", "581-022-2055 Career Education") + CHAR(10) + IF([Rule 581-022-2060]@row = "1", "581-022-2060 Comprehensive School Counseling") + CHAR(10) + IF([Rule 581-022-2100]@row = "1", "581-022-2100 Administration of State Assessments") + CHAR(10) + IF([Rule 581-022-2110]@row = "1", "581-022-2110 Exception of Students with Disabilities from State Assessments") + CHAR(10) + IF([Rule 581-022-2115]@row = "1", "581-022-2115 Assessment of Essential Skills") + CHAR(10) + IF([Rule 581-022-2120]@row = "1", "581-022-2120 Essential Skill Assessments for English Language Learners") + CHAR(10) + IF([Rule 581-022-2130]@row = "1", "581-022-2130 Kindergarten Assessment") + CHAR(10) + IF([Rule 581-022-2205]@row = "1", "581-022-2205 Policies on Reporting of Child Abuse") + CHAR(10) + IF([Rule 581-022-2210]@row = "1", "581-022-2210 Anabolic Steroids and Performance Enhancing Substances") + CHAR(10) + IF([Rule 581-022-2215]@row = "1", "581-022-2215 Safety of School Sports - Concussions") + CHAR(10) + IF([Rule 581-022-2220]@row = "1", "581-022-2220 Health Services") + CHAR(10) + IF([Rule 581-022-2223]@row = "1", "581-022-2223 Healthy and Safe Schools Plan") + CHAR(10) + IF([Rule 581-022-2225]@row = "1", "581-022-2225 Emergency Plans and Safety Programs") + CHAR(10) + IF([Rule 581-022-2230]@row = "1", "581-022-2230 Asbestos Management Plans") + CHAR(10) + IF([Rule 581-022-2250]@row = "1", "581-022-2250 District Improvement Plan") + CHAR(10) + IF([Rule 581-022-2255]@row = "1", "581-022-2255 School and District Performance Report Criteria") + CHAR(10) + IF([Rule 581-022-2260]@row = "1", "581-022-2260 Records and Reports") + CHAR(10) + IF([Rule 581-022-2263]@row = "1", "581-022-2263 Physical Education Requirements") + CHAR(10) + IF([Rule 581-022-2265]@row = "1", "581-022-2265 Report on PE Data") + CHAR(10) + IF([Rule 581-022-2267]@row = "1", "581-022-2267 Annual Report on Use of Restraint and Seclusion") + CHAR(10) + IF([Rule 581-022-2270]@row = "1", "581-022-2270 Individual Student Assessment, Recordkeeping and Reporting") + CHAR(10) + IF([Rule 581-022-2300]@row = "1", "581-022-2300 Standardization") + CHAR(10) + IF([Rule 581-022-2305]@row = "1", "581-022-2305 Operating Policies and Procedures") + CHAR(10) + IF([Rule 581-022-2308]@row = "1", "581-022-2308 Agreements Entered into Voluntarily with Organizations") + CHAR(10) + IF([Rule 581-022-2310]@row = "1", "581-022-2310 Equal Education Opportunities") + CHAR(10) + IF([Rule 581-022-2312]@row = "1", "581-022-2312 Every Student Belongs") + CHAR(10) + IF([Rule 581-022-2315]@row = "1", "581-022-2315 Special Education for Children with Disabilities") + CHAR(10) + IF([Rule 581-022-2320]@row = "1", "581-022-2320 Required Instructional Time") + CHAR(10) + IF([Rule 581-022-2325]@row = "1", "581-022-2325 Identification of Academically Talented and Intellectually Gifted Students") + CHAR(10) + IF([Rule 581-022-2330]@row = "1", "581-022-2330 Rights of Parents of TAG Students") + CHAR(10) + IF([Rule 581-022-2335]@row = "1", "581-022-2335 Daily Class Size") + CHAR(10) + IF([Rule 581-022-2340]@row = "1", "581-022-2340 Media Programs"+ CHAR(10) + ([Rule 581-022-2345]@row = "1", "581-022-2345 Auxiliary Services") + CHAR(10) + IF([Rule 581-022-2350]@row = "1", "581-022-2350 Independent Adoptions of Instructional Materials") + CHAR(10) + IF([Rule 581-022-2355]@row = "1", "581-022-2355 Instructional Materials Adoption") + CHAR(10) + IF([Rule 581-022-2360]@row = "1", "581-022-2360 Postponement of Purchase of State-Adopted Instructional Materials") + CHAR(10) + IF([Rule 581-022-2370]@row = "1", "581-022-2370 Complaint Procedure") + CHAR(10) + IF([Rule 581-022-2400]@row = "1", "581-022-2400 Personnel") + CHAR(10) + IF([Rule 581-022-2405]@row = "1", "581-022-2405 Personnel Policies") + CHAR(10) + IF([Rule 581-022-2410]@row = "1", "581-022-2410 Teacher and Administrator Evaluation and Support") + CHAR(10) + IF([Rule 581-022-2415]@row = "1", "581-022-2415 Core Teaching Standards") + CHAR(10) + IF([Rule 581-022-2420]@row = "1", "581-022-2420 Educational Leadership - Administrator Standards") + CHAR(10) + IF([Rule 581-022-2430]@row = "1", "581-022-2430 Fingerprinting of Subject Individuals in Positions not Requiring Licensure as Teachers, Administrators, Personnel Specialists, School Nurses") + CHAR(10) + IF([Rule 581-022-2440]@row = "1", "581-022-2440 Teacher Training Related to Dyslexia") + CHAR(10) + IF([Rule 581-022-2445]@row = "1", "581-022-2445 Universal Screening for Risk Factors of Dyslexia") + CHAR(10) + IF([Rule 581-022-2500]@row = "1", "581-022-2500 Programs and Services for TAG Students") + CHAR(10) + IF([Rule 581-022-2505]@row = "1", "581-022-2505 Alternative Education Programs") + CHAR(10) + IF([Rule 581-022-2510]@row = "1", "581-022-2510 Suicide Prevention Plan") + CHAR(10) + IF([Rule PE Part 2]@row = "1", "PE Part 2" + CHAR(10))

pzupan

Thank you, there was one there originally but that's the point where I needed to break it up. Is there a better way to accomplish the same goal? I am new to Smartsheet so this may very well be a rubber band and paperclip method to accomplish this

Paul Newcome

I personally would break it down into a few helper columns (4 or 5) then join each of those helper columns together. One thing to keep in mind though... A JOIN function will only output 4,000 characters including spaces, so if you have a lot of boxes checked then you may run into an issue of not all of the outputs being displayed.