List the Expired Dates

S

smscheung

New Member
Joined
Mar 14, 2019
Messages
26
If column A are names, and Column B are expiry dates for their cards, is it possible that I open a new spreadsheet, and draw only the expired name & date on the sheet? I have multiple of cards that required to draw expiry date to another sheet, will the formula be similar?

Thanks for your help.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Maybe something like this:

Excel Workbook
AB
1NameExpired Date
2Name15/2/2019
3Name44/8/2019
4Name56/12/2019
5Name73/22/2019
Expired Dates
Excel Workbook
AB
1NameDate
2Name15/2/2019
3Name28/14/2019
4Name35/6/2020
5Name44/8/2019
6Name56/12/2019
7Name67/22/2019
8Name73/22/2019
Data
 
Upvote 0
Hi, for some reason only for AhoyNC only partial formula is showing up, correct me if I am wrong but I think he is going for this in Column A2:

Code: 
=IFERROR(INDEX(Sheet1!$A$2:$A$8,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1)/(Sheet1!$B$2:$B$8<TODAY()),ROWS(A$2:A8),"")
 
Upvote 0
thanks for your help, but when I use the A2 formula, it keeps saying too few arguments for this function, am I missing something?
 
Upvote 0
Formula in A2 should be (not sure why it got cut off).
Remove the space between the < . Had to put spaces around to get formula to post due to HTML code.
Code: 
[TABLE="width: 1061"]
<tbody>[TR]
[TD="width: 1061"]<today()),rows($a$2:a2))),"")< td=""></today()),rows($a$2:a2))),"")<>[TABLE]
<tbody>[TR]
[TD]IFERROR(INDEX(Sheet1!$A$2:$A$8,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1)/(Sheet1!$B$2:$B$8   < TODAY()),ROWS($A$2:A2))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Like this?

Excel Workbook
AB
1NameExpired Date
2Name15/2/2019
3Name56/12/2019
4Name73/22/2019
Expired Dates
Excel Workbook
AB
1NameExpired Date
2Name15/2/2019
3Name28/14/2019
4Name35/6/2020
5Name4
6Name56/12/2019
7Name67/22/2019
8Name73/22/2019
Raw Data
 
Upvote 0
Try changing formula in A2 of above example to
Code: 
  [COLOR=black][FONT=Calibri]=IFERROR(INDEX(Sheet1!$A$2:$A$8,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$2)+1[/FONT][/COLOR][FONT=Calibri])[COLOR=red]/((Sheet1!$B$2:$B$8 < TODAY())*(Sheet1!$B$2:$B$8 < > 0)),[/COLOR][COLOR=black]ROWS($A$2:A2))),"")[/COLOR][/FONT]
  <!--[if gte mso 9]><xml>  <o:OfficeDocumentSettings>   <o:AllowPNG/>  </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml>  <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:TrackMoves/>   <w:TrackFormatting/>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:DoNotPromoteQF/>   <w:LidThemeOther>EN-US</w:LidThemeOther>   <w:LidThemeAsian>X-NONE</w:LidThemeAsian>   <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>    <w:SplitPgBreakAndParaMark/>    <w:EnableOpenTypeKerning/>    <w:DontFlipMirrorIndents/>    <w:OverrideTableStyleHps/>   </w:Compatibility>   <m:mathPr>    <m:mathFont m:val="Cambria Math"/>    <m:brkBin m:val="before"/>    <m:brkBinSub m:val="--"/>    <m:smallFrac m:val="off"/>    <m:dispDef/>    <m:lMargin m:val="0"/>    <m:rMargin m:val="0"/>    <m:defJc m:val="centerGroup"/>    <m:wrapIndent m:val="1440"/>    <m:intLim m:val="subSup"/>    <m:naryLim m:val="undOvr"/>   </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml>  <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="false"   DefSemiHidden="false" DefQFormat="false" DefPriority="99"   LatentStyleCount="376">   <w:LsdException Locked="false" Priority="0" QFormat="true" Name="Normal"/>   <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 1"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 2"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 3"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 4"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 5"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 6"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 7"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 8"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 9"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 6"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 7"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 8"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 9"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 1"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 2"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 3"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 4"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 5"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 6"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 7"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 8"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 9"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Normal Indent"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="footnote text"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="annotation text"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="header"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="footer"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index heading"/>   <w:LsdException Locked="false" Priority="35" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="caption"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="table of figures"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="envelope address"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="envelope return"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="footnote reference"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="annotation reference"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="line number"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="page number"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="endnote reference"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="endnote text"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="table of authorities"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="macro"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="toa heading"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Bullet"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Number"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Bullet 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Bullet 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Bullet 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Bullet 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Number 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Number 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Number 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Number 5"/>   <w:LsdException Locked="false" Priority="10" QFormat="true" Name="Title"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Closing"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Signature"/>   <w:LsdException Locked="false" Priority="1" SemiHidden="true"    UnhideWhenUsed="true" Name="Default Paragraph Font"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text Indent"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Continue"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Continue 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Continue 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Continue 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Continue 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Message Header"/>   <w:LsdException Locked="false" Priority="11" QFormat="true" Name="Subtitle"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Salutation"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Date"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text First Indent"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text First Indent 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Note Heading"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text Indent 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text Indent 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Block Text"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Hyperlink"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="FollowedHyperlink"/>   <w:LsdException Locked="false" Priority="22" QFormat="true" Name="Strong"/>   <w:LsdException Locked="false" Priority="20" QFormat="true" Name="Emphasis"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Document Map"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Plain Text"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="E-mail Signature"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Top of Form"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Bottom of Form"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Normal (Web)"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Acronym"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Address"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Cite"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Code"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Definition"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Keyboard"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Preformatted"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Sample"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Typewriter"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Variable"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Normal Table"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="annotation subject"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="No List"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Outline List 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Outline List 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Outline List 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Simple 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Simple 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Simple 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Classic 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Classic 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Classic 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Classic 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Colorful 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Colorful 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Colorful 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Columns 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Columns 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Columns 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Columns 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Columns 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 6"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 7"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 8"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 6"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 7"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 8"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table 3D effects 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table 3D effects 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table 3D effects 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Contemporary"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Elegant"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Professional"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Subtle 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Subtle 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Web 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Web 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Web 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Balloon Text"/>   <w:LsdException Locked="false" Priority="39" Name="Table Grid"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Theme"/>   <w:LsdException Locked="false" SemiHidden="true" Name="Placeholder Text"/>   <w:LsdException Locked="false" Priority="1" QFormat="true" Name="No Spacing"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading"/>   <w:LsdException Locked="false" Priority="61" Name="Light List"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 1"/>   <w:LsdException Locked="false" Priority="61" Name="Light List Accent 1"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 1"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 1"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 1"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 1"/>   <w:LsdException Locked="false" SemiHidden="true" Name="Revision"/>   <w:LsdException Locked="false" Priority="34" QFormat="true"    Name="List Paragraph"/>   <w:LsdException Locked="false" Priority="29" QFormat="true" Name="Quote"/>   <w:LsdException Locked="false" Priority="30" QFormat="true"    Name="Intense Quote"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 1"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 1"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 1"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 1"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 1"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 1"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 1"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 1"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 2"/>   <w:LsdException Locked="false" Priority="61" Name="Light List Accent 2"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 2"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 2"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 2"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 2"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 2"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 2"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 2"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 2"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 2"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 2"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 2"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 2"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 3"/>   <w:LsdException Locked="false" Priority="61" Name="Light List Accent 3"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 3"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 3"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 3"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 3"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 3"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 3"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 3"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 3"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 3"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 3"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 3"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 3"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 4"/>   <w:LsdException Locked="false" Priority="61" Name="Light List Accent 4"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 4"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 4"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 4"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 4"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 4"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 4"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 4"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 4"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 4"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 4"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 4"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 4"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 5"/>   <w:LsdException Locked="false" Priority="61" Name="Light List Accent 5"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 5"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 5"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 5"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 5"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 5"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 5"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 5"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 5"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 5"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 5"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 5"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 5"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 6"/>   <w:LsdException Locked="false" Priority="61" Name="Light List Accent 6"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 6"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 6"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 6"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 6"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 6"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 6"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 6"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 6"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 6"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 6"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 6"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 6"/>   <w:LsdException Locked="false" Priority="19" QFormat="true"    Name="Subtle Emphasis"/>   <w:LsdException Locked="false" Priority="21" QFormat="true"    Name="Intense Emphasis"/>   <w:LsdException Locked="false" Priority="31" QFormat="true"    Name="Subtle Reference"/>   <w:LsdException Locked="false" Priority="32" QFormat="true"    Name="Intense Reference"/>   <w:LsdException Locked="false" Priority="33" QFormat="true" Name="Book Title"/>   <w:LsdException Locked="false" Priority="37" SemiHidden="true"    UnhideWhenUsed="true" Name="Bibliography"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="TOC Heading"/>   <w:LsdException Locked="false" Priority="41" Name="Plain Table 1"/>   <w:LsdException Locked="false" Priority="42" Name="Plain Table 2"/>   <w:LsdException Locked="false" Priority="43" Name="Plain Table 3"/>   <w:LsdException Locked="false" Priority="44" Name="Plain Table 4"/>   <w:LsdException Locked="false" Priority="45" Name="Plain Table 5"/>   <w:LsdException Locked="false" Priority="40" Name="Grid Table Light"/>   <w:LsdException Locked="false" Priority="46" Name="Grid Table 1 Light"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark"/>   <w:LsdException Locked="false" Priority="51" Name="Grid Table 6 Colorful"/>   <w:LsdException Locked="false" Priority="52" Name="Grid Table 7 Colorful"/>   <w:LsdException Locked="false" Priority="46"    Name="Grid Table 1 Light Accent 1"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 1"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 1"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 1"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 1"/>   <w:LsdException Locked="false" Priority="51"    Name="Grid Table 6 Colorful Accent 1"/>   <w:LsdException Locked="false" Priority="52"    Name="Grid Table 7 Colorful Accent 1"/>   <w:LsdException Locked="false" Priority="46"    Name="Grid Table 1 Light Accent 2"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 2"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 2"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 2"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 2"/>   <w:LsdException Locked="false" Priority="51"    Name="Grid Table 6 Colorful Accent 2"/>   <w:LsdException Locked="false" Priority="52"    Name="Grid Table 7 Colorful Accent 2"/>   <w:LsdException Locked="false" Priority="46"    Name="Grid Table 1 Light Accent 3"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 3"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 3"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 3"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 3"/>   <w:LsdException Locked="false" Priority="51"    Name="Grid Table 6 Colorful Accent 3"/>   <w:LsdException Locked="false" Priority="52"    Name="Grid Table 7 Colorful Accent 3"/>   <w:LsdException Locked="false" Priority="46"    Name="Grid Table 1 Light Accent 4"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 4"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 4"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 4"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 4"/>   <w:LsdException Locked="false" Priority="51"    Name="Grid Table 6 Colorful Accent 4"/>   <w:LsdException Locked="false" Priority="52"    Name="Grid Table 7 Colorful Accent 4"/>   <w:LsdException Locked="false" Priority="46"    Name="Grid Table 1 Light Accent 5"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 5"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 5"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 5"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 5"/>   <w:LsdException Locked="false" Priority="51"    Name="Grid Table 6 Colorful Accent 5"/>   <w:LsdException Locked="false" Priority="52"    Name="Grid Table 7 Colorful Accent 5"/>   <w:LsdException Locked="false" Priority="46"    Name="Grid Table 1 Light Accent 6"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 6"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 6"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 6"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 6"/>   <w:LsdException Locked="false" Priority="51"    Name="Grid Table 6 Colorful Accent 6"/>   <w:LsdException Locked="false" Priority="52"    Name="Grid Table 7 Colorful Accent 6"/>   <w:LsdException Locked="false" Priority="46" Name="List Table 1 Light"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark"/>   <w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful"/>   <w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful"/>   <w:LsdException Locked="false" Priority="46"    Name="List Table 1 Light Accent 1"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 1"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 1"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 1"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 1"/>   <w:LsdException Locked="false" Priority="51"    Name="List Table 6 Colorful Accent 1"/>   <w:LsdException Locked="false" Priority="52"    Name="List Table 7 Colorful Accent 1"/>   <w:LsdException Locked="false" Priority="46"    Name="List Table 1 Light Accent 2"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 2"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 2"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 2"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 2"/>   <w:LsdException Locked="false" Priority="51"    Name="List Table 6 Colorful Accent 2"/>   <w:LsdException Locked="false" Priority="52"    Name="List Table 7 Colorful Accent 2"/>   <w:LsdException Locked="false" Priority="46"    Name="List Table 1 Light Accent 3"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 3"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 3"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 3"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 3"/>   <w:LsdException Locked="false" Priority="51"    Name="List Table 6 Colorful Accent 3"/>   <w:LsdException Locked="false" Priority="52"    Name="List Table 7 Colorful Accent 3"/>   <w:LsdException Locked="false" Priority="46"    Name="List Table 1 Light Accent 4"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 4"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 4"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 4"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 4"/>   <w:LsdException Locked="false" Priority="51"    Name="List Table 6 Colorful Accent 4"/>   <w:LsdException Locked="false" Priority="52"    Name="List Table 7 Colorful Accent 4"/>   <w:LsdException Locked="false" Priority="46"    Name="List Table 1 Light Accent 5"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 5"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 5"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 5"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 5"/>   <w:LsdException Locked="false" Priority="51"    Name="List Table 6 Colorful Accent 5"/>   <w:LsdException Locked="false" Priority="52"    Name="List Table 7 Colorful Accent 5"/>   <w:LsdException Locked="false" Priority="46"    Name="List Table 1 Light Accent 6"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 6"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 6"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 6"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 6"/>   <w:LsdException Locked="false" Priority="51"    Name="List Table 6 Colorful Accent 6"/>   <w:LsdException Locked="false" Priority="52"    Name="List Table 7 Colorful Accent 6"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Mention"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Smart Hyperlink"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Hashtag"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Unresolved Mention"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Smart Link"/>  </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>  /* Style Definitions */  table.MsoNormalTable 	{mso-style-name:"Table Normal"; 	mso-tstyle-rowband-size:0; 	mso-tstyle-colband-size:0; 	mso-style-noshow:yes; 	mso-style-priority:99; 	mso-style-parent:""; 	mso-padding-alt:0in 5.4pt 0in 5.4pt; 	mso-para-margin-top:0in; 	mso-para-margin-right:0in; 	mso-para-margin-bottom:8.0pt; 	mso-para-margin-left:0in; 	line-height:107%; 	mso-pagination:widow-orphan; 	font-size:11.0pt; 	font-family:"Calibri",sans-serif; 	mso-ascii-font-family:Calibri; 	mso-ascii-theme-font:minor-latin; 	mso-hansi-font-family:Calibri; 	mso-hansi-theme-font:minor-latin; 	mso-bidi-font-family:"Times New Roman"; 	mso-bidi-theme-font:minor-bidi;} </style> <![endif]-->
 
Upvote 0
so I can paste this formula all the way down? Doesn't seem to work on my worksheet.. I have over 1000 names that need to be done. It is picking the ones that are not expired
 
Last edited:
Upvote 0
I assume you changed the ranges to match your data. If not that would need to be done.
In the example I gave I used any data less than today to be expired. If you have something different you need to tell us. It would help if you would post a small sample of your data and the expected results.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question Question
Conditional formatting
Replies
3
Views
384
SanjayGMusafir
SanjayGMusafir
A
  • Solved
lookup question
Replies
3
Views
133
ajm
A
A
  • Question Question
Conditional formatting icon sets for expiry 12months from date in cell
Replies
6
Views
264
MARK858
MARK858
S
  • Solved
Copying a row to another tab when condition is met on expiry date
2
Replies
16
Views
1K
kevin9999
K
H
  • Question Question
Expiring Date formula?
Replies
5
Views
142
Alex Blakenburg
A

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top