Excel - Mastering Excel Formulas: Find Out Who Hasn't Signed in 6 Easy Steps - Episode 1869

3,088 views · Published 14 March 2014 · 17:21 · Indexed 4 November 2026

Channel: MrExcel.com · 2014 · Education

Watch on YouTube

Microsoft Excel Tutorial: Trying to write an insane formula to solve another poorly formatted spreadsheet. Up to six people need to sign each document. Write a formula to figure out who has not signed.

In this Excel tutorial, we dive into the world of Excel formulas to solve a common problem: figuring out which of six people haven't signed a contract. We'll go through the process step by step.

📌 We start by using a helper formula to pinpoint where the contract begins.
📌 Discover how to match the contract and find the rows that relate to it.
📌 We'll check for a match with the six names involved.
📌 While some might use OFFSET, we'll show you a non-volatile alternative using the INDEX function.
📌 The formula reveals that Dylan is the one who hasn't signed yet.
📌 Learn how to prevent errors with ISNA.

But wait, there's more! Bill mentions an alternative method involving Mike Girvin and the old MoreFunc add-in, although it's been missing since Excel 2010. Mike demonstrates a dynamic range approach to see if any of these names aren't in the list. We'll use the OFFSET function, select the first column, handle duplicates, employ the ROWS function, and finalize the formula with Ctrl+Shift+Enter. The result? A dynamic range delivered through OFFSET, which can be used in defined names.

Don't let Excel mysteries baffle you. Join us in unraveling this complex formula challenge and mastering your Excel skills! If you find this video helpful, don't forget to like, share, and subscribe to our channel for more Excel tips and tutorials.

Table of Contents
(00:00) Dueling Excel: Who has not signed?
(00:45) Six people have to sign
(01:25) Formula to detect start of document
(01:45) How many rows in this contract?
(02:28) MATCH with colon and INDEX
(03:25) Building list of people who did not sign
(04:44) MoreFunc Add-in
(05:02) Build a Dynamic Range
(05:26) Any of these NOT in the list
(05:38) OFFSET function
(06:03) Locked but relative down
(06:48) Using ROWS function
(07:38) SI named Range
(08:23) RE named range
(09:00) CO for COUNT
(11:16) AGGREGATE function
(12:34) Compare Both Lists
(15:07) Add Criteria when copying to side
(16:43) Summary from Bill

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelduel

This video answers these common search terms:
Array formula in Excel
Count function in Excel
Countif function in Excel
Dynamic range in Excel
Excel signature spots
Index formula in Excel
Index lookup in Excel
Join function in Excel
Match function in Excel
Offset function in Excel
Vlookup function in Excel
YouTube Excel tutorial

This video answers these common search terms:
Array formula in Excel
Count function in Excel
Countif function in Excel
Dynamic range in Excel
Excel signature spots
Index formula in Excel
Index lookup in Excel
Join function in Excel
Match function in Excel
Offset function in Excel
Vlookup function in Excel
YouTube Excel tutorial

This video answers these common search terms:
Array formula in Excel
Count function in Excel
Countif function in Excel
Dynamic range in Excel
Excel signature spots
Index formula in Excel
Index lookup in Excel
Join function in Excel
Match function in Excel
Offset function in Excel
Vlookup function in Excel
YouTube Excel tutorial

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153694/

More from this channel