+ Reply to Thread
Results 1 to 7 of 7

Use Conditional Formatting or Formulas to Identify Invoices not formatted properly

  1. #1
    Registered User
    Join Date
    11-20-2018
    Location
    New Hampshire
    MS-Off Ver
    MS Office 2016
    Posts
    3

    Use Conditional Formatting or Formulas to Identify Invoices not formatted properly

    Hi All - New to the thread. I have a data set (image below) and I'm hoping to find a way either through a formula or conditional formatting to identify lines not following our invoice number convention (USINV-######) and requiring massaging prior to upload.

    InvoiceNumbers.JPG

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Use Conditional Formatting or Formulas to Identify Invoices not formatted properly

    as a way custom format "USINV-"000000
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: Use Conditional Formatting or Formulas to Identify Invoices not formatted properly

    Or using Cf, use a formula
    =LEFT(F2,6)<>"USINV-"

  4. #4
    Registered User
    Join Date
    11-20-2018
    Location
    New Hampshire
    MS-Off Ver
    MS Office 2016
    Posts
    3

    Re: Use Conditional Formatting or Formulas to Identify Invoices not formatted properly

    This almost gets me there... is the way to augment this to also validate the right six characters are a six digit number string??

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: Use Conditional Formatting or Formulas to Identify Invoices not formatted properly

    how about
    =AND(LEN(F2)<>12,OR(LEFT(F2,6)<>"USINV-",ISNUMBER(RIGHT(F2,6)*1)))

  6. #6
    Registered User
    Join Date
    11-20-2018
    Location
    New Hampshire
    MS-Off Ver
    MS Office 2016
    Posts
    3

    Re: Use Conditional Formatting or Formulas to Identify Invoices not formatted properly

    This seems to do the trick! Have a new added wrinkle where an invoice formatted "FINV########" is acceptable too. How can I modify the formula to accommodate this??

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: Use Conditional Formatting or Formulas to Identify Invoices not formatted properly

    Maybe
    =OR(AND(LEFT(F2,4)<>"FINV",LEFT(F2,6)<>"USINV-"),NOT(ISNUMBER(RIGHT(F2,6)*1)),LEN(F2)<>12)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional Formatting not working properly
    By Bmiha in forum Excel General
    Replies: 3
    Last Post: 07-10-2018, 04:45 AM
  2. [SOLVED] Sum cells formatted by conditional formatting
    By hugowhere in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-14-2016, 11:25 AM
  3. Sum conditional formatted cells / SUMIFS formulas
    By jorrb in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-29-2015, 09:04 AM
  4. [SOLVED] Formula to identify cancelled invoices
    By The_Snook in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-21-2015, 07:26 PM
  5. [SOLVED] Conditional formatting does not function properly
    By kelwood in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-06-2012, 12:29 PM
  6. Replies: 4
    Last Post: 06-08-2011, 03:56 PM
  7. Replies: 2
    Last Post: 08-16-2009, 06:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1