Leveraging smart controls in smart formulas

I set out to create a List to power dynamic formulas that provide Sort options. I found that there were so many choices, and created too many Nested IF Statements. It was a nightmare!

Simple CHOOSE Statement instead of a Nested IF

A set of simple, straightforward Nested IF statements doesn’t have to stretch the grey matter too much, but what if the TRUE and FALSE components contain some fancy formulas? Is there a way to have a sort of IF THEN ELSE ELSE ELSE ELSE ELSE …?

Using a Ranged Name to define a single cell
=IF(NameSorter=Normal_Name_Order,
   PROPER([@Student]),
   RIGHT([@Student],LEN([@Student])-FIND(" ",[@Student])) & ", " & 
PROPER(LEFT([@Student], SEARCH(" ", [@Student]) - 1)) )
)

Normal_Name_Order is a single-cell Named Range in the drive an in-cell list at NameSorter. Having that single value as a Named Range means that if the text in the cell Normal_Name_Order was changed from ‘Normal Names’ to say, ‘First Name first’ then the formula above would be not be broken, as would be the case if the IF statement referenced the cell text for a direct match, e.g.

A static reference in a formula can easily lead to a broken formula. E.g.
=IF(NameSorter="Normal Names",
   PROPER([@Student]),
   RIGHT([@Student],LEN([@Student])-FIND(" ",[@Student])) & ", " & PROPER(LEFT([@Student], SEARCH(" ", [@Student]) - 1)) )
)

Having IF statements refer to things that might be changed later can result in broken workings.

Definitions

[@Student] is the Table form of a reference, checking out the matching cell in Student column of the same row as the target formula.

Name_Sorter shows two options:

  • Normal Names
  • Last Name first

These options drive the following results:

  • The full Student Name
    e.g. Bilbo Baggins
  • The Last Name(s), a comma and space and the First Name
    e.g. Baggins, Bilbo

‘CHOOSE’ Function instead of ‘IF’ Function!

If the logic can reference an integer as the logical_test then the horizons can open with a flood of white space around your arcane workings. We can ditch IF and nested IF‘s

Here is a CHOOSE statement hitting the NameSorter cell (NameSorter is a single-cell Named Range) to have CHOOSE select the first set of instructions or the second set of instructions.

=CHOOSE(NameSorter,
  PROPER([@Student]),
  RIGHT([@Student],LEN([@Student])-FIND(" ",[@Student])) & ", " & PROPER(LEFT([@Student], SEARCH(" ", [@Student]) - 1)) )
 )

The visual clarity is achieved by using [Alt] + [Enter] to create line breaks.

  • PROPER converts a text string to proper case
  • RIGHT returns the number of characaters from the end of a t4ext string
  • LEN returns the number of characters in a text string
  • FIND returns the starting character of a text string within another text string
  • LEFT returns the number of characaters from the start of a t4ext string
  • SEARCH returns the number of the character at which a specific character or text string is first found
So what if you can’t use a Form Control but you still want to use CHOOSE?

What do you do if – for some reason – you can’t use a straightforward Form control but you still want to ditch Nested IF’s containing complex formulas, for a CHOOSE Statement containing those same complex formulas but in a much better visual space for editing and maintenance?

I had a (relatively) straightforward IF statement with two choices; Sort Location and Sort Student. It worked, it was messy, but serviceable:

Here’s the code it drives:

=IF(OR(ISBLANK([@Student]),[@[Sort Order]]="N",[@[Sort Order]]="L"),
      IF(Sort_Include=Sort_Location,LEFT([@[Location]],3) & " | ","") &
      [@Student] & " (" & TEXT([@[Date]],"DD|MM") & ", " & [@[Course]] & ")",
    " ["& [@[Course]]&"] " & IF(Sort_Include=Sort_Location,LEFT([@[     Location]],3) & " | ","") & IF(ISBLANK([@[Sort Order]]),"",TEXT([@[Sort Order]],"00 ")) & PROPER(LEFT([@Student], SEARCH(" ", [@Student]) - 1))&", " &
     LEFT(RIGHT([@Student], LEN([@Student]) - SEARCH("#", SUBSTITUTE([@Student]," ", "#", LEN([@Student]) - LEN(SUBSTITUTE([@Student], " ", ""))))),3)
  )

The first block concatenates cells in that Table row to give:

 [B1] Cl | First Name, LLL

Where LLL is the first three characters of the Last Name.

If Sort Student is selected:

The second block kicks in, giving:

 [B1] First Name, LLL
Stripping out the class location

I had the (not so) bright idea of adding more options, but that would mean a staggering amount of Nested IF’s. Ugh. What could one do?

One got inventive, is what. Renamed list options so that they are prefixed by an integer which can be stripped by a Left function to drive the index_number of a CHOOSE statement.

As you can see, it got me my six sort options. That is:

  1. [Location] EST Class List [Student]
  2. [Location] aXcellerate #/T [Student]
  3. Location | Student
  4. Block | Location (#) Name / Block | Name
  5. Location | Student (Date | Block) / Enrolled | Location | Student (Date | Block)
  6. Block | Location (#) Name / Enrolled | Block | Location | Name

I shudder to think of Nexted IF’s that complex, but the CHOOSE function let me lay out the cell formula neatly. I can muck it around, re-visit it and intuitively see what’s what.

=IF([@[     Date]]=0,"z",
     IFERROR(CHOOSE(LEFT(Sort_Picker,SEARCH(" ",Sort_Picker)-1),
       [@[     Location]] & " | " & IF([@[    Sort Class List]]="",[@Student],TEXT([@[    Sort Class List]],"00") & "  " & [@Student]),
       IF([@[    Sort aXcellerate]]="","ZZ",[@[     Location]] & " | " & TEXT([@[    Sort aXcellerate]],"00") & "  " & [@Student]),
       LEFT([@[     Location]],3) & " |  " & [@Student],
       " [" &  [@[     Course]] & "]  " & LEFT([@[     Location]],3) & " | " &
           IF(ISBLANK([@[    Sort aXcellerate]]),"",TEXT([@[    Sort aXcellerate]],"00 ")) &

           PROPER(LEFT([@Student], SEARCH(" ", [@Student]) - 1))&", " & LEFT(RIGHT([@Student], LEN([@Student]) - SEARCH("#", SUBSTITUTE([@Student]," ", "#", LEN([@Student]) - LEN(SUBSTITUTE([@Student], " ", ""))))),3),
       IF([@[B1  Enrolment]]="E","","x - ") & LEFT([@[     Location]],3) & " | " & [@Student] & " (" & TEXT([@[     Date]],"DD|MM") & ", " & [@[     Course]] & ")",
       IF([@[B1  Enrolment]]="E","","x -  ") &"[" &  [@[     Course]] & "] " &
           LEFT([@[     Location]],3) & " | " &
           IF(ISBLANK([@[    Sort aXcellerate]]),"",TEXT([@[    Sort aXcellerate]],"00 ")) &

           PROPER(LEFT([@Student], SEARCH(" ", [@Student]) - 1))&", " & LEFT(RIGHT([@Student], LEN([@Student]) - SEARCH("#", SUBSTITUTE([@Student]," ", "#", LEN([@Student]) - LEN(SUBSTITUTE([@Student], " ", ""))))),3),
   ),
"Select a Sort option (J1) to use this feature")
)

OK, complicated. However, heaps less messy than not using CHOOSE.

The trick was to prefix options with an integer and use a LEFT function to pull just that first character for the ‘test’ (index_number) that CHOOSE uses to determine which result to give:

LEFT(Sort_Picker,SEARCH(” “,Sort_Picker)-1)

If I was able to use a Form control instead in-cell validation to drive a list, I could simplify the CHOOSE statement and put the following into the index_number instead:

                             Sort_Picker

I ended up with a cute control. Go me!

A bit overboard really, but I wanted a quick way to apply complex sorting without having to go to the Sort dialog and set multiple sorts each time.