pattern-repetition-formula-creating tool

group

Count

f for copy

formula

pattern

for column info

Collaboration powered by Smartsheet | Report Abuse

=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 9)), CONTAINS(INDEX({Multi-Select Dropdown List}, 9), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

100

=IFERROR(MID([Prep List]@row, FIND([DL2]#, SUBSTITUTE([Prep List]@row, [DL1]#, [DL2]#, 1)) + 1, FIND([DL2]#, SUBSTITUTE([Prep List]@row, [DL1]#, [DL2]#, 2)) - FIND([DL2]#, SUBSTITUTE([Prep List]@row, [DL1]#, [DL2]#, 1)) - 1), "")

[DL2]#, 2

[DL2]#, 1

120

=IF(HAS([Not Complete]@row, INDEX({Multiple list : List}, 1)), IF(HAS(CompletedA@row, INDEX({Multiple list : List}, 1)), 1, 0), 0) + IF(HAS([Not Complete]@row, INDEX({Multiple list : List}, 1)), IF(HAS(CompletedB@row, INDEX({Multiple list : List}, 1)), 1, 0), 0) + IF(HAS([Not Complete]@row, INDEX({Multiple list : List}, 1)), IF(HAS(CompletedC@row, INDEX({Multiple list : List}, 1)), 1, 0), 0)

List}, 1

140

=IF(ISBLANK([R1]@row), "", INDEX(Start:Start, VALUE([R1]@row)))

144

=IFERROR(JOIN(COLLECT({Dashboarding Annual Calendar : Task Name}, {Dashboarding Annual Calendar : Parent}, [Task Name]@row, {Dashboarding Annual Calendar : Show}, true, {Dashboarding Annual Calendar : Start}, MONTH(@cell) >= 8, {Dashboarding Annual Calendar : Finish}, MONTH(@cell) <= 8), CHAR(10)), "")

145

'=IFERROR(JOIN(COLLECT({Dashboarding Annual Calendar : Task Name}, {Dashboarding Annual Calendar : Parent}, [Task Name]@row, {Dashboarding Annual Calendar : Show}, true, {Dashboarding Annual Calendar : Start}, MONTH(@cell) >= 9, {Dashboarding Annual Calendar : Finish}, MONTH(@cell) <= 9), CHAR(10)), "")

=IFERROR(JOIN(COLLECT({Dashboarding Annual Calendar : Task Name}, {Dashboarding Annual Calendar : Parent}, [Task Name]@row, {Dashboarding Annual Calendar : Show}, true, {Dashboarding Annual Calendar : Start}, MONTH(@cell) >= 9, {Dashboarding Annual Calendar : Finish}, MONTH(@cell) <= 9), CHAR(10)), "")

146

'=IFERROR(JOIN(COLLECT({Dashboarding Annual Calendar : Task Name}, {Dashboarding Annual Calendar : Parent}, [Task Name]@row, {Dashboarding Annual Calendar : Show}, true, {Dashboarding Annual Calendar : Start}, MONTH(@cell) >= 10, {Dashboarding Annual Calendar : Finish}, MONTH(@cell) <= 10), CHAR(10)), "")

=IFERROR(JOIN(COLLECT({Dashboarding Annual Calendar : Task Name}, {Dashboarding Annual Calendar : Parent}, [Task Name]@row, {Dashboarding Annual Calendar : Show}, true, {Dashboarding Annual Calendar : Start}, MONTH(@cell) >= 10, {Dashboarding Annual Calendar : Finish}, MONTH(@cell) <= 10), CHAR(10)), "")

'=IFERROR(JOIN(COLLECT({Dashboarding Annual Calendar : Task Name}, {Dashboarding Annual Calendar : Parent}, [Task Name]@row, {Dashboarding Annual Calendar : Show}, true, {Dashboarding Annual Calendar : Start}, MONTH(@cell) >= 10, {Dashboarding Annual Calendar : Finish}, MONTH(@cell) <= 10), CHAR(10)), "")

147

'=IFERROR(JOIN(COLLECT({Dashboarding Annual Calendar : Task Name}, {Dashboarding Annual Calendar : Parent}, [Task Name]@row, {Dashboarding Annual Calendar : Show}, true, {Dashboarding Annual Calendar : Start}, MONTH(@cell) >= 11, {Dashboarding Annual Calendar : Finish}, MONTH(@cell) <= 11), CHAR(10)), "")

=IFERROR(JOIN(COLLECT({Dashboarding Annual Calendar : Task Name}, {Dashboarding Annual Calendar : Parent}, [Task Name]@row, {Dashboarding Annual Calendar : Show}, true, {Dashboarding Annual Calendar : Start}, MONTH(@cell) >= 11, {Dashboarding Annual Calendar : Finish}, MONTH(@cell) <= 11), CHAR(10)), "")

'=IFERROR(JOIN(COLLECT({Dashboarding Annual Calendar : Task Name}, {Dashboarding Annual Calendar : Parent}, [Task Name]@row, {Dashboarding Annual Calendar : Show}, true, {Dashboarding Annual Calendar : Start}, MONTH(@cell) >= 11, {Dashboarding Annual Calendar : Finish}, MONTH(@cell) <= 11), CHAR(10)), "")

148

'=IFERROR(JOIN(COLLECT({Dashboarding Annual Calendar : Task Name}, {Dashboarding Annual Calendar : Parent}, [Task Name]@row, {Dashboarding Annual Calendar : Show}, true, {Dashboarding Annual Calendar : Start}, MONTH(@cell) >= 12, {Dashboarding Annual Calendar : Finish}, MONTH(@cell) <= 12), CHAR(10)), "")

=IFERROR(JOIN(COLLECT({Dashboarding Annual Calendar : Task Name}, {Dashboarding Annual Calendar : Parent}, [Task Name]@row, {Dashboarding Annual Calendar : Show}, true, {Dashboarding Annual Calendar : Start}, MONTH(@cell) >= 12, {Dashboarding Annual Calendar : Finish}, MONTH(@cell) <= 12), CHAR(10)), "")

'=IFERROR(JOIN(COLLECT({Dashboarding Annual Calendar : Task Name}, {Dashboarding Annual Calendar : Parent}, [Task Name]@row, {Dashboarding Annual Calendar : Show}, true, {Dashboarding Annual Calendar : Start}, MONTH(@cell) >= 12, {Dashboarding Annual Calendar : Finish}, MONTH(@cell) <= 12), CHAR(10)), "")

149

=IF(COUNT(ANCESTORS()) > 0, AND(Start@row <= PARENT(Start@row) + 0, End@row >= PARENT(Start@row) + 0, End@row <= PARENT(End@row)))

+ 0

182

183

=IF(ISDATE([D1]@row), IF(ISDATE([S1]@row), IF(AND([S1]@row <= [D1]@row, [E1]@row >= [D1]@row), 1, 0), 0) + IF(ISDATE([S2]@row), IF(AND([S2]@row <= [D1]@row, [E2]@row >= [D1]@row), 1, 0), 0) + IF(ISDATE([S3]@row), IF(AND([S3]@row <= [D1]@row, [E3]@row >= [D1]@row), 1, 0), 0) + IF(ISDATE([S4]@row), IF(AND([S4]@row <= [D1]@row, [E4]@row >= [D1]@row), 1, 0), 0))

[D1

203

=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 2), "")

G10}, 2

204

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 3), "")

=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 3), "")

G10}, 3

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 3), "")

205

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 4), "")

=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 4), "")

G10}, 4

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 4), "")

206

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 5), "")

=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 5), "")

G10}, 5

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 5), "")

207

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 6), "")

=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 6), "")

G10}, 6

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 6), "")

208

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 7), "")

=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 7), "")

G10}, 7

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 7), "")

209

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 8), "")

=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 8), "")

G10}, 8

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 8), "")

210

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 9), "")

=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 9), "")

G10}, 9

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 9), "")

211

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 10), "")

=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 10), "")

G10}, 10

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 10), "")

212

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 11), "")

=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 11), "")

G10}, 11

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 11), "")

213

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 12), "")

=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 12), "")

G10}, 12

'=IFERROR(VLOOKUP(Class@row, {Class - Group Mapping Range : Class to G10}, 12), "")

214

=IF([D2]@row + 1 <= [End Date]@row, [D2]@row + 1)

@cell)), 2

222

=IFERROR(SUBSTITUTE([D-1]@row, [D2]@row, ""), "")

-1

231

232

=IF(NOT(CONTAINS(CHAR(10), [D-1]@row)), [D-1]@row, MID([D-1]@row, 1, FIND(CHAR(10), [D-1]@row)))

-1

242

=IF(AND([Start Date]@row <= TODAY(1), TODAY(1) <= [End Date]@row), 1)

248

249

250

251

=IF([Is Parent]@row, INDEX({Sheet B: Attendance_Register : Date}, 1) + "", IF(ISBLANK([Employee ID]@row), "", IF(HAS({us-federal-holidays : date}, INDEX({Sheet B: Attendance_Register : Date}, 1)), "Holiday", IF(OR(WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 1)) = 1, WEEKDAY(INDEX({Sheet B: Attendance_Register : Date}, 1)) = 7), "Weekend", IF(COUNTIFS(INDEX({Sheet B: Attendance_Register : Leave Checked}, 0, [Employee ID Index]@row), 1, {Sheet B: Attendance_Register : Date}, INDEX({Sheet B: Attendance_Register : Date}, 1)) > 0, "On Leave", IF(CONTAINS("Complete", JOIN(COLLECT({Sheet C : Timesheet : Entry Status}, {Sheet C : Timesheet : Employee ID}, [Employee ID]@row, {Sheet C : Timesheet Range : Date}, INDEX({Sheet B: Attendance_Register : Date}, 1)))), "Complete", "Pending"))))))

Date}, 1

282

=IF([Row ID]@row = 1, 0, IF(INDEX([Qual1]:[Qual1], [Row ID]@row - 1) > [Qual1]@row, 0, INDEX([Qual1]:[Qual1], [Row ID]@row - 1)))

Qual1

292

=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 1)), CONTAINS(INDEX({Multi-Select Dropdown List}, 1), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

293

'=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 2)), CONTAINS(INDEX({Multi-Select Dropdown List}, 2), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 2)), CONTAINS(INDEX({Multi-Select Dropdown List}, 2), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

294

'=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 3)), CONTAINS(INDEX({Multi-Select Dropdown List}, 3), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 3)), CONTAINS(INDEX({Multi-Select Dropdown List}, 3), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

295

'=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 4)), CONTAINS(INDEX({Multi-Select Dropdown List}, 4), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 4)), CONTAINS(INDEX({Multi-Select Dropdown List}, 4), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

296

'=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 5)), CONTAINS(INDEX({Multi-Select Dropdown List}, 5), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 5)), CONTAINS(INDEX({Multi-Select Dropdown List}, 5), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

297

'=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 6)), CONTAINS(INDEX({Multi-Select Dropdown List}, 6), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 6)), CONTAINS(INDEX({Multi-Select Dropdown List}, 6), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

298

'=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 7)), CONTAINS(INDEX({Multi-Select Dropdown List}, 7), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 7)), CONTAINS(INDEX({Multi-Select Dropdown List}, 7), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

299

'=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 8)), CONTAINS(INDEX({Multi-Select Dropdown List}, 8), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 8)), CONTAINS(INDEX({Multi-Select Dropdown List}, 8), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

300

'=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 9)), CONTAINS(INDEX({Multi-Select Dropdown List}, 9), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

301

'=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 10)), CONTAINS(INDEX({Multi-Select Dropdown List}, 10), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

=IF(HAS([Keywords (Multi-Select Dropdown)]@row, INDEX({Multi-Select Dropdown List}, 10)), CONTAINS(INDEX({Multi-Select Dropdown List}, 10), INDEX({Search Text: Text}, MATCH(ID@row, {Search Text: ID}, 0))))

302

=MOD(SUMIFS(INDEX({Group Qualification Assignment Range 1:10}, 0, 1), {Group Qualification Assignment Range : Row ID}, <=[Row ID]@row), INDEX({Number of Qualified and Required: Qualified}, 1))

312

=IF([Count M]@row > 4, SUBSTITUTE([List-4]@row, [Opt5]@row, ""))

> 4

List-4

Opt5

328

=IF(COUNTM@row > 3, SUBSTITUTE([TC-2]@row, [TC3]@row, ""))

> 3

[TC-2

TC3

335

=IF(COUNTM@row > 3, SUBSTITUTE([TC-2]@row, [TC3]@row, ""))

TC2

([TC -1

341

342

=IF(COUNTM@row > 3, LEFT([Access-2]@row, FIND(CHAR(10), [Access-2]@row)), [AC2]@row)

Access-2

345

346

347

348

=SUBSTITUTE([Access-1]@row, [AC2]@row, "")

Access-1

AC2

354

355

=IF([Distinct Assigned]@row >= 2, CHAR(10) + CHAR(10) + UPPER(INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 2)) + ":" + CHAR(10) + JOIN(COLLECT(CHILDREN([Resource Summary]@row), CHILDREN([Assigned To]@row), INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 2)), CHAR(10)))

>= 2

@row)), 2

374

375

376

=IFERROR(IF(ISTEXT([L4]@row), LEFT(SUBSTITUTE(List¬@row, JOIN([L1]@row:[L4]@row, DLM#) + DLM#, ""), FIND(DLM#, SUBSTITUTE(List¬@row, JOIN([L1]@row:[L4]@row, DLM#) + DLM#, "")) - 1)), "")

393

394

=IFERROR(MID(List@row, [c10]@row, [c11]@row - [c10]@row), "")

[c10

[c11

414

=IFERROR(INDEX(SUCCESSORS([Task Name]@row), 1, 3), "")

422

=IFERROR(IF(AND(COUNTIFS(COLLECT([Row No]:[Row No], [Pred 3]:[Pred 3], 1), CONTAINS(@cell, JOIN(SUCCESSORS([Task Name]@row), ", "))) > 0, [is Before]@row), 1), "")

Pred 3

430

431

432

433

434

435

436

=IF([FilterType3]@row = "Country", Country@row, IF([FilterType3]@row = "Department", Department@row, IF([FilterType3]@row = "Ethnisity", Ethnicity@row, IF([FilterType3]@row = "Business Unit", [Business Unit]@row, IF([FilterType3]@row = "Job Title", [Job Title]@row)))))

FilterType3

439

'=IF([FilterType1]@row = "Country", Country@row, IF([FilterType1]@row = "Department", Department@row, IF([FilterType1]@row = "Ethnisity", Ethnicity@row, IF([FilterType1]@row = "Business Unit", [Business Unit]@row, IF([FilterType1]@row = "Job Title", [Job Title]@row)))))

FilterType1

442

=SUM(

'=SUM(IF(HAS([Recurrence Day Names (weekly)]@row, "Monday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Monday"), {month_name}, HAS(@cell, [Month_]@row)), 0)+IF(HAS([Recurrence Day Names (weekly)]@row, "Tuesday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Tuesday"), {month_name}, HAS(@cell, [Month_]@row)), 0)+IF(HAS([Recurrence Day Names (weekly)]@row, "Wednesday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Wednesday"), {month_name}, HAS(@cell, [Month_]@row)), 0)+IF(HAS([Recurrence Day Names (weekly)]@row, "Thursday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Thursday"), {month_name}, HAS(@cell, [Month_]@row)), 0)+IF(HAS([Recurrence Day Names (weekly)]@row, "Friday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Friday"), {month_name}, HAS(@cell, [Month_]@row)), 0)

=SUM(IF(HAS([Recurrence Day Names (weekly)]@row, "Monday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Monday"), {month_name}, HAS(@cell, [Month_]@row)), 0)+IF(HAS([Recurrence Day Names (weekly)]@row, "Tuesday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Tuesday"), {month_name}, HAS(@cell, [Month_]@row)), 0)+IF(HAS([Recurrence Day Names (weekly)]@row, "Wednesday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Wednesday"), {month_name}, HAS(@cell, [Month_]@row)), 0)+IF(HAS([Recurrence Day Names (weekly)]@row, "Thursday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Thursday"), {month_name}, HAS(@cell, [Month_]@row)), 0)+IF(HAS([Recurrence Day Names (weekly)]@row, "Friday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Friday"), {month_name}, HAS(@cell, [Month_]@row)), 0)

IF(HAS([Recurrence Day Names (weekly)]@row, "Monday"), COUNTIFS({day_of_week_name}, HAS(@cell, "Monday"), {month_name}, HAS(@cell, [Month_]@row)), 0)+

Monday

448

=AVERAGE(

'=AVERAGE(IF(HAS([Day of Week Selection]@row, "Mon"), COUNTIFS({Day of Week}, HAS(@cell, "Mon"), {month number}, HAS(@cell, Month@row))+IF(HAS([Day of Week Selection]@row, "Tue"), COUNTIFS({Day of Week}, HAS(@cell, "Tue"), {month number}, HAS(@cell, Tueth@row))+IF(HAS([Day of Week Selection]@row, "Wed"), COUNTIFS({Day of Week}, HAS(@cell, "Wed"), {month number}, HAS(@cell, Wedth@row))+IF(HAS([Day of Week Selection]@row, "Thu"), COUNTIFS({Day of Week}, HAS(@cell, "Thu"), {month number}, HAS(@cell, Thuth@row))+IF(HAS([Day of Week Selection]@row, "Fri"), COUNTIFS({Day of Week}, HAS(@cell, "Fri"), {month number}, HAS(@cell, Frith@row))

=AVERAGE(IF(HAS([Day of Week Selection]@row, "Mon"), COUNTIFS({Day of Week}, HAS(@cell, "Mon"), {month number}, HAS(@cell, Month@row))+IF(HAS([Day of Week Selection]@row, "Tue"), COUNTIFS({Day of Week}, HAS(@cell, "Tue"), {month number}, HAS(@cell, Tueth@row))+IF(HAS([Day of Week Selection]@row, "Wed"), COUNTIFS({Day of Week}, HAS(@cell, "Wed"), {month number}, HAS(@cell, Wedth@row))+IF(HAS([Day of Week Selection]@row, "Thu"), COUNTIFS({Day of Week}, HAS(@cell, "Thu"), {month number}, HAS(@cell, Thuth@row))+IF(HAS([Day of Week Selection]@row, "Fri"), COUNTIFS({Day of Week}, HAS(@cell, "Fri"), {month number}, HAS(@cell, Frith@row))

IF(HAS([Day of Week Selection]@row, "Mon"), COUNTIFS({Day of Week}, HAS(@cell, "Mon"), {month number}, HAS(@cell, Month@row))+

Mon

454

=IF([m#]@row > value1, FIND(CHAR(10), [Multiple Email List]@row, [cvalue2]@row + 1))

value1

value2

474

=IF([m#]@row > 3, FIND(CHAR(10), make@row, [c2]@row + 1), 0)

492

=IF([m#]@row >value1, MID(make@row, [value2]@row + 1, [value3]@row - [value2]@row), IF([m#]@row =value1, MID(make@row, [value2]@row + 1, LEN(make@row) - [value2]@row + 1)))

value1

value2

value3

513

'=IF(INDEX(DISTINCT(make:make), 1) = make@row, RIGHT("0"+1,2) ,IF(INDEX(DISTINCT(make:make), 2) = make@row, RIGHT("0"+2,2) ,IF(INDEX(DISTINCT(make:make), 3) = make@row, RIGHT("0"+3,2) ,IF(INDEX(DISTINCT(make:make), 4) = make@row, RIGHT("0"+4,2) ,IF(INDEX(DISTINCT(make:make), 5) = make@row, RIGHT("0"+5,2) ,IF(INDEX(DISTINCT(make:make), 6) = make@row, RIGHT("0"+6,2) ,IF(INDEX(DISTINCT(make:make), 7) = make@row, RIGHT("0"+7,2) ,IF(INDEX(DISTINCT(make:make), 8) = make@row, RIGHT("0"+8,2) ,IF(INDEX(DISTINCT(make:make), 9) = make@row, RIGHT("0"+9,2) ,IF(INDEX(DISTINCT(make:make), 10) = make@row, RIGHT("0"+10,2) ,IF(INDEX(DISTINCT(make:make), 11) = make@row, RIGHT("0"+11,2) ,IF(INDEX(DISTINCT(make:make), 12) = make@row, RIGHT("0"+12,2) ,IF(INDEX(DISTINCT(make:make), 13) = make@row, RIGHT("0"+13,2) ,IF(INDEX(DISTINCT(make:make), 14) = make@row, RIGHT("0"+14,2) ,IF(INDEX(DISTINCT(make:make), 15) = make@row, RIGHT("0"+15,2) ,IF(INDEX(DISTINCT(make:make), 16) = make@row, RIGHT("0"+16,2) ,IF(INDEX(DISTINCT(make:make), 17) = make@row, RIGHT("0"+17,2) ,IF(INDEX(DISTINCT(make:make), 18) = make@row, RIGHT("0"+18,2) ,IF(INDEX(DISTINCT(make:make), 19) = make@row, RIGHT("0"+19,2) ,IF(INDEX(DISTINCT(make:make), 20) = make@row, RIGHT("0"+20,2) ,IF(INDEX(DISTINCT(make:make), 21) = make@row, RIGHT("0"+21,2) ,IF(INDEX(DISTINCT(make:make), 22) = make@row, RIGHT("0"+22,2) ,IF(INDEX(DISTINCT(make:make), 23) = make@row, RIGHT("0"+23,2) ,IF(INDEX(DISTINCT(make:make), 24) = make@row, RIGHT("0"+24,2) ,IF(INDEX(DISTINCT(make:make), 25) = make@row, RIGHT("0"+25,2) ,IF(INDEX(DISTINCT(make:make), 26) = make@row, RIGHT("0"+26,2) ,IF(INDEX(DISTINCT(make:make), 27) = make@row, RIGHT("0"+27,2) ,IF(INDEX(DISTINCT(make:make), 28) = make@row, RIGHT("0"+28,2) ,IF(INDEX(DISTINCT(make:make), 29) = make@row, RIGHT("0"+29,2) ,IF(INDEX(DISTINCT(make:make), 30) = make@row, RIGHT("0"+30,2) ,IF(INDEX(DISTINCT(make:make), 31) = make@row, RIGHT("0"+31,2) ,IF(INDEX(DISTINCT(make:make), 32) = make@row, RIGHT("0"+32,2) ,IF(INDEX(DISTINCT(make:make), 33) = make@row, RIGHT("0"+33,2) ,IF(INDEX(DISTINCT(make:make), 34) = make@row, RIGHT("0"+34,2) ,IF(INDEX(DISTINCT(make:make), 35) = make@row, RIGHT("0"+35,2) ,IF(INDEX(DISTINCT(make:make), 36) = make@row, RIGHT("0"+36,2) ,IF(INDEX(DISTINCT(make:make), 37) = make@row, RIGHT("0"+37,2) ,IF(INDEX(DISTINCT(make:make), 38) = make@row, RIGHT("0"+38,2) ,IF(INDEX(DISTINCT(make:make), 39) = make@row, RIGHT("0"+39,2) ,IF(INDEX(DISTINCT(make:make), 40) = make@row, RIGHT("0"+40,2) ,IF(INDEX(DISTINCT(make:make), 41) = make@row, RIGHT("0"+41,2) ,IF(INDEX(DISTINCT(make:make), 42) = make@row, RIGHT("0"+42,2) ,IF(INDEX(DISTINCT(make:make), 43) = make@row, RIGHT("0"+43,2) ,IF(INDEX(DISTINCT(make:make), 44) = make@row, RIGHT("0"+44,2) ,IF(INDEX(DISTINCT(make:make), 45) = make@row, RIGHT("0"+45,2) ,IF(INDEX(DISTINCT(make:make), 46) = make@row, RIGHT("0"+46,2) ,IF(INDEX(DISTINCT(make:make), 47) = make@row, RIGHT("0"+47,2) ,IF(INDEX(DISTINCT(make:make), 48) = make@row, RIGHT("0"+48,2) ,IF(INDEX(DISTINCT(make:make), 49) = make@row, RIGHT("0"+49,2) ,IF(INDEX(DISTINCT(make:make), 50) = make@row, RIGHT("0"+50,2)

=IF(INDEX(DISTINCT(make:make), 1) = make@row, RIGHT("0"+1,2) ,IF(INDEX(DISTINCT(make:make), 2) = make@row, RIGHT("0"+2,2) ,IF(INDEX(DISTINCT(make:make), 3) = make@row, RIGHT("0"+3,2) ,IF(INDEX(DISTINCT(make:make), 4) = make@row, RIGHT("0"+4,2) ,IF(INDEX(DISTINCT(make:make), 5) = make@row, RIGHT("0"+5,2) ,IF(INDEX(DISTINCT(make:make), 6) = make@row, RIGHT("0"+6,2) ,IF(INDEX(DISTINCT(make:make), 7) = make@row, RIGHT("0"+7,2) ,IF(INDEX(DISTINCT(make:make), 8) = make@row, RIGHT("0"+8,2) ,IF(INDEX(DISTINCT(make:make), 9) = make@row, RIGHT("0"+9,2) ,IF(INDEX(DISTINCT(make:make), 10) = make@row, RIGHT("0"+10,2) ,IF(INDEX(DISTINCT(make:make), 11) = make@row, RIGHT("0"+11,2) ,IF(INDEX(DISTINCT(make:make), 12) = make@row, RIGHT("0"+12,2) ,IF(INDEX(DISTINCT(make:make), 13) = make@row, RIGHT("0"+13,2) ,IF(INDEX(DISTINCT(make:make), 14) = make@row, RIGHT("0"+14,2) ,IF(INDEX(DISTINCT(make:make), 15) = make@row, RIGHT("0"+15,2) ,IF(INDEX(DISTINCT(make:make), 16) = make@row, RIGHT("0"+16,2) ,IF(INDEX(DISTINCT(make:make), 17) = make@row, RIGHT("0"+17,2) ,IF(INDEX(DISTINCT(make:make), 18) = make@row, RIGHT("0"+18,2) ,IF(INDEX(DISTINCT(make:make), 19) = make@row, RIGHT("0"+19,2) ,IF(INDEX(DISTINCT(make:make), 20) = make@row, RIGHT("0"+20,2) ,IF(INDEX(DISTINCT(make:make), 21) = make@row, RIGHT("0"+21,2) ,IF(INDEX(DISTINCT(make:make), 22) = make@row, RIGHT("0"+22,2) ,IF(INDEX(DISTINCT(make:make), 23) = make@row, RIGHT("0"+23,2) ,IF(INDEX(DISTINCT(make:make), 24) = make@row, RIGHT("0"+24,2) ,IF(INDEX(DISTINCT(make:make), 25) = make@row, RIGHT("0"+25,2) ,IF(INDEX(DISTINCT(make:make), 26) = make@row, RIGHT("0"+26,2) ,IF(INDEX(DISTINCT(make:make), 27) = make@row, RIGHT("0"+27,2) ,IF(INDEX(DISTINCT(make:make), 28) = make@row, RIGHT("0"+28,2) ,IF(INDEX(DISTINCT(make:make), 29) = make@row, RIGHT("0"+29,2) ,IF(INDEX(DISTINCT(make:make), 30) = make@row, RIGHT("0"+30,2) ,IF(INDEX(DISTINCT(make:make), 31) = make@row, RIGHT("0"+31,2) ,IF(INDEX(DISTINCT(make:make), 32) = make@row, RIGHT("0"+32,2) ,IF(INDEX(DISTINCT(make:make), 33) = make@row, RIGHT("0"+33,2) ,IF(INDEX(DISTINCT(make:make), 34) = make@row, RIGHT("0"+34,2) ,IF(INDEX(DISTINCT(make:make), 35) = make@row, RIGHT("0"+35,2) ,IF(INDEX(DISTINCT(make:make), 36) = make@row, RIGHT("0"+36,2) ,IF(INDEX(DISTINCT(make:make), 37) = make@row, RIGHT("0"+37,2) ,IF(INDEX(DISTINCT(make:make), 38) = make@row, RIGHT("0"+38,2) ,IF(INDEX(DISTINCT(make:make), 39) = make@row, RIGHT("0"+39,2) ,IF(INDEX(DISTINCT(make:make), 40) = make@row, RIGHT("0"+40,2) ,IF(INDEX(DISTINCT(make:make), 41) = make@row, RIGHT("0"+41,2) ,IF(INDEX(DISTINCT(make:make), 42) = make@row, RIGHT("0"+42,2) ,IF(INDEX(DISTINCT(make:make), 43) = make@row, RIGHT("0"+43,2) ,IF(INDEX(DISTINCT(make:make), 44) = make@row, RIGHT("0"+44,2) ,IF(INDEX(DISTINCT(make:make), 45) = make@row, RIGHT("0"+45,2) ,IF(INDEX(DISTINCT(make:make), 46) = make@row, RIGHT("0"+46,2) ,IF(INDEX(DISTINCT(make:make), 47) = make@row, RIGHT("0"+47,2) ,IF(INDEX(DISTINCT(make:make), 48) = make@row, RIGHT("0"+48,2) ,IF(INDEX(DISTINCT(make:make), 49) = make@row, RIGHT("0"+49,2) ,IF(INDEX(DISTINCT(make:make), 50) = make@row, RIGHT("0"+50,2)

IF(INDEX(DISTINCT(make:make), 1) = make@row, RIGHT("0"+1,2) ,

564

565

'=IF(Level@row = 1, IF(OR(ISBLANK([SQ1]#), [SQ1]# = "Num"), IF(OR(ISBLANK([LN1]#), [LN1]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN1]#)), IF([SQ1]# = "Upper", IF(OR(ISBLANK([LN1]#), [LN1]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN1]#)), IF([SQ1]# = "Lower", IF(OR(ISBLANK([LN1]#), [LN1]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN1]#)))))),IF(Level@row = 2, IF(OR(ISBLANK([SQ2]#), [SQ2]# = "Num"), IF(OR(ISBLANK([LN2]#), [LN2]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN2]#)), IF([SQ2]# = "Upper", IF(OR(ISBLANK([LN2]#), [LN2]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN2]#)), IF([SQ2]# = "Lower", IF(OR(ISBLANK([LN2]#), [LN2]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN2]#)))))),IF(Level@row = 3, IF(OR(ISBLANK([SQ3]#), [SQ3]# = "Num"), IF(OR(ISBLANK([LN3]#), [LN3]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN3]#)), IF([SQ3]# = "Upper", IF(OR(ISBLANK([LN3]#), [LN3]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN3]#)), IF([SQ3]# = "Lower", IF(OR(ISBLANK([LN3]#), [LN3]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN3]#)))))),IF(Level@row = 4, IF(OR(ISBLANK([SQ4]#), [SQ4]# = "Num"), IF(OR(ISBLANK([LN4]#), [LN4]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN4]#)), IF([SQ4]# = "Upper", IF(OR(ISBLANK([LN4]#), [LN4]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN4]#)), IF([SQ4]# = "Lower", IF(OR(ISBLANK([LN4]#), [LN4]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN4]#)))))),IF(Level@row = 5, IF(OR(ISBLANK([SQ5]#), [SQ5]# = "Num"), IF(OR(ISBLANK([LN5]#), [LN5]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN5]#)), IF([SQ5]# = "Upper", IF(OR(ISBLANK([LN5]#), [LN5]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN5]#)), IF([SQ5]# = "Lower", IF(OR(ISBLANK([LN5]#), [LN5]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN5]#)))))),IF(Level@row = 6, IF(OR(ISBLANK([SQ6]#), [SQ6]# = "Num"), IF(OR(ISBLANK([LN6]#), [LN6]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN6]#)), IF([SQ6]# = "Upper", IF(OR(ISBLANK([LN6]#), [LN6]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN6]#)), IF([SQ6]# = "Lower", IF(OR(ISBLANK([LN6]#), [LN6]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN6]#)))))),IF(Level@row = 7, IF(OR(ISBLANK([SQ7]#), [SQ7]# = "Num"), IF(OR(ISBLANK([LN7]#), [LN7]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN7]#)), IF([SQ7]# = "Upper", IF(OR(ISBLANK([LN7]#), [LN7]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN7]#)), IF([SQ7]# = "Lower", IF(OR(ISBLANK([LN7]#), [LN7]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN7]#)))))),IF(Level@row = 8, IF(OR(ISBLANK([SQ8]#), [SQ8]# = "Num"), IF(OR(ISBLANK([LN8]#), [LN8]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN8]#)), IF([SQ8]# = "Upper", IF(OR(ISBLANK([LN8]#), [LN8]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN8]#)), IF([SQ8]# = "Lower", IF(OR(ISBLANK([LN8]#), [LN8]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN8]#)))))),IF(Level@row = 9, IF(OR(ISBLANK([SQ9]#), [SQ9]# = "Num"), IF(OR(ISBLANK([LN9]#), [LN9]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN9]#)), IF([SQ9]# = "Upper", IF(OR(ISBLANK([LN9]#), [LN9]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN9]#)), IF([SQ9]# = "Lower", IF(OR(ISBLANK([LN9]#), [LN9]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN9]#))))))

=IF(Level@row = 1, IF(OR(ISBLANK([SQ1]#), [SQ1]# = "Num"), IF(OR(ISBLANK([LN1]#), [LN1]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN1]#)), IF([SQ1]# = "Upper", IF(OR(ISBLANK([LN1]#), [LN1]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN1]#)), IF([SQ1]# = "Lower", IF(OR(ISBLANK([LN1]#), [LN1]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN1]#)))))),IF(Level@row = 2, IF(OR(ISBLANK([SQ2]#), [SQ2]# = "Num"), IF(OR(ISBLANK([LN2]#), [LN2]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN2]#)), IF([SQ2]# = "Upper", IF(OR(ISBLANK([LN2]#), [LN2]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN2]#)), IF([SQ2]# = "Lower", IF(OR(ISBLANK([LN2]#), [LN2]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN2]#)))))),IF(Level@row = 3, IF(OR(ISBLANK([SQ3]#), [SQ3]# = "Num"), IF(OR(ISBLANK([LN3]#), [LN3]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN3]#)), IF([SQ3]# = "Upper", IF(OR(ISBLANK([LN3]#), [LN3]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN3]#)), IF([SQ3]# = "Lower", IF(OR(ISBLANK([LN3]#), [LN3]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN3]#)))))),IF(Level@row = 4, IF(OR(ISBLANK([SQ4]#), [SQ4]# = "Num"), IF(OR(ISBLANK([LN4]#), [LN4]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN4]#)), IF([SQ4]# = "Upper", IF(OR(ISBLANK([LN4]#), [LN4]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN4]#)), IF([SQ4]# = "Lower", IF(OR(ISBLANK([LN4]#), [LN4]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN4]#)))))),IF(Level@row = 5, IF(OR(ISBLANK([SQ5]#), [SQ5]# = "Num"), IF(OR(ISBLANK([LN5]#), [LN5]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN5]#)), IF([SQ5]# = "Upper", IF(OR(ISBLANK([LN5]#), [LN5]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN5]#)), IF([SQ5]# = "Lower", IF(OR(ISBLANK([LN5]#), [LN5]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN5]#)))))),IF(Level@row = 6, IF(OR(ISBLANK([SQ6]#), [SQ6]# = "Num"), IF(OR(ISBLANK([LN6]#), [LN6]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN6]#)), IF([SQ6]# = "Upper", IF(OR(ISBLANK([LN6]#), [LN6]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN6]#)), IF([SQ6]# = "Lower", IF(OR(ISBLANK([LN6]#), [LN6]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN6]#)))))),IF(Level@row = 7, IF(OR(ISBLANK([SQ7]#), [SQ7]# = "Num"), IF(OR(ISBLANK([LN7]#), [LN7]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN7]#)), IF([SQ7]# = "Upper", IF(OR(ISBLANK([LN7]#), [LN7]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN7]#)), IF([SQ7]# = "Lower", IF(OR(ISBLANK([LN7]#), [LN7]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN7]#)))))),IF(Level@row = 8, IF(OR(ISBLANK([SQ8]#), [SQ8]# = "Num"), IF(OR(ISBLANK([LN8]#), [LN8]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN8]#)), IF([SQ8]# = "Upper", IF(OR(ISBLANK([LN8]#), [LN8]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN8]#)), IF([SQ8]# = "Lower", IF(OR(ISBLANK([LN8]#), [LN8]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN8]#)))))),IF(Level@row = 9, IF(OR(ISBLANK([SQ9]#), [SQ9]# = "Num"), IF(OR(ISBLANK([LN9]#), [LN9]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN9]#)), IF([SQ9]# = "Upper", IF(OR(ISBLANK([LN9]#), [LN9]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN9]#)), IF([SQ9]# = "Lower", IF(OR(ISBLANK([LN9]#), [LN9]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN9]#))))))

IF(Level@row = 1, IF(OR(ISBLANK([SQ1]#), [SQ1]# = "Num"), IF(OR(ISBLANK([LN1]#), [LN1]# = "Any"), LC@row, RIGHT("000000000" + LC@row, [LN1]#)), IF([SQ1]# = "Upper", IF(OR(ISBLANK([LN1]#), [LN1]# = "Any"), CHAR(LC@row + 64), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 64)), [LN1]#)), IF([SQ1]# = "Lower", IF(OR(ISBLANK([LN1]#), [LN1]# = "Any"), CHAR(LC@row + 96), LEFT(SUBSTITUTE("^^^^^^^^^^", "^", CHAR(LC@row + 96)), [LN1]#)))))),

575

=SUM(

'=SUM(=IF(AND(91 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 91 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(93 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 93 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(94 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 94 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(100 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 100 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(101 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 101 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(103 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 103 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(104 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 104 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(110 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 110 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(111 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 111 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(113 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 113 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(114 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 114 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(120 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 120 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(121 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 121 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(123 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 123 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(124 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 124 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(130 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 130 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(131 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 131 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(133 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 133 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(134 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 134 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(140 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 140 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(141 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 141 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(143 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 143 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(144 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 144 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(150 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 150 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(151 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 151 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(153 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 153 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(154 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 154 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(160 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 160 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(161 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 161 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(163 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 163 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(164 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 164 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(170 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 170 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(171 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 171 <= VALUE(LEFT(SUBSTITUTE(End@

=SUM(=IF(AND(91 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 91 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(93 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 93 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(94 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 94 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(100 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 100 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(101 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 101 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(103 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 103 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(104 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 104 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(110 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 110 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(111 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 111 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(113 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 113 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(114 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 114 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(120 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 120 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(121 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 121 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(123 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 123 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(124 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 124 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(130 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 130 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(131 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 131 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(133 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 133 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(134 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 134 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(140 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 140 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(141 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 141 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(143 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 143 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(144 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 144 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(150 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 150 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(151 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 151 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(153 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 153 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(154 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 154 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(160 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 160 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(161 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 161 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(163 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 163 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(164 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 164 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(170 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 170 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)=IF(AND(171 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 171 <= VALUE(LEFT(SUBSTITUTE(End@r

=IF(AND(90 >= VALUE(LEFT(SUBSTITUTE(Begin@row, ":", ""), 2)), 90 <= VALUE(LEFT(SUBSTITUTE(End@row, ":", ""), 2))), 1, 0)

634

=IF(AND(84 >= VALUE(SUBSTITUTE(LEFT(Begin@row, LEN(Begin@row) - 1), ":", "")), 84 <= VALUE(SUBSTITUTE(LEFT(End@row, LEN(End@row) - 1), ":", ""))), 1, 0)

696

=IF([90]@row = 0, "Empty", IF([91]@row = 0, "Empty", IF([93]@row = 0, "Half", "Full")))

711

=IF([83]@row = 0, "Empty", IF([84]@row = 0, "Empty", IF([90]@row = 0, "Half", "Full")))

727

=LEFT(MID(Task@row, 3 * BarChars# + 1, BarChars#) + Fill#, BarChars#)

3 *

731

=IF([110]@row = 1, IF([103]@row = 0, [Bar1]@row, IF([100]@row = 0, [Bar2]@row, IF([93]@row = 0, [Bar3]@row, IF([90]@row = 0, [Bar4]@row, IF([83]@row = 0, [Bar5]@row, IF(Count@row > 5, Fill#, IF(Count@row > 6, Fill#, IF(Count@row > 7, Fill#, IF(Count@row > 8, Fill#))))))))))

110

103

100

754

=IF(cntm@row > 3, FIND(",", [Assigned To]@row, [c2]@row + 1))

> 3

[c2

772

=IF(cntm@row = 3, MID([Assigned To]@row, [c2]@row + 2, LEN([Assigned To]@row) - [c2]@row), IF(cntm@row > 3, MID([Assigned To]@row, [c2]@row + 2, [c3]@row - [c2]@row - 2)))

[c2

790

'=IF([9 AM]@row, IF(COUNTIFS(Created:Created, <Created@row, [Pick-up Date]:[Pick-up Date], [Pick-up Date]@row, [9 AM]:[9 AM], 1) = 0, 1, 0))

9 AM

796

=IF(ISTEXT([m1]@row), INDEX({Email}, MATCH([m1]@row, {Name}, 0)))

816

=IF(AND([S #]@row <= 6, 6 <= [E #]@row), 1, 0)

834

=IFERROR(INDEX(COLLECT([Row #]:[Row #], Successors:Successors, CONTAINS(Sep# + [Row #]@row + Sep#, @cell)), 1), "")

844

=IFERROR(INDEX(SUCCESSORS([Task Name]@row), 1,1), "")

855

=IFERROR(INDEX(Status:Status, [Pred 1]@row),"")

866

=IFERROR(IF(COUNTIFS(COLLECT([Row No]:[Row No], [p1]:[p1], 1), CONTAINS(@cell, [Suc 2]@row)) > 0, 1, 0), "")

875

=IFERROR(INDEX(COLLECT({List}, {List}, CONTAINS(@cell, [Multiple Dropdown List]@row)), 51), "")