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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920