+ Reply to Thread
Results 1 to 10 of 10

Conditional format diff cell, List with no Duplicate, & Report Monthly-Date to Date

  1. 06-02-2018, 12:53 AM #1
    n4t4nes
    n4t4nes is offline
    Registered User
    Join Date
    12-04-2017
    Location
    Indonesia
    MS-Off Ver
    MS Office 2013
    Posts
    17

    Conditional format diff cell, List with no Duplicate, & Report Monthly-Date to Date

    Hello Guru,

    I have 4 cases
    1. Conditional Format
      I want "In Invoice[Partner] if there are no text from Product[Partner] list, then give the Invoice[Partner] colour"
      I'am very annoyed when use validation, it keeps reminding me, because i need to fill a lot of data outside the Product[Partner]
      The problem is conditional format always ask for True value
      I've managed colouring Invoice[Count] in same row, but that's not what I want
      Is it possible?
    2. List with no Duplicate
      I want "No Duplication on List"
      Example:
      When I used "=LOOKUP(2;1/(COUNTIF(P_List;Product[Partner])=0);Product[Partner])" to get unique list, the result "0"
      So i can't make a list from unique list
      Can Anybody help me?
    3. Partner Monthly Report
      I want "When i choose Partner_Choice and Monthly_Choice, i can get the Report"
      First I used"{=IFERROR(SMALL(IF(Invoice[Partner]=Partner_Choice;ROW(Invoice[Partner]);"");I23);"")} to get Partner Report, and It's Work
      And then I tried 2 logic"{=IFERROR(SMALL(IF(AND(Invoice[Partner]=Partner_Choice;Invoice[Month]=Month_Choice);ROW(Invoice[Partner]);"");I23);"")}, It's failed
      What should i do?
    4. Partner Date to Date Report
      I want "When i choose Partner_Choice, From_Choice, & To_Choice, i can get the Report
      I think, when case no 3 solved, then case no 4 can solved too"

    I will Attach my file, thank you for your precious time
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-02-2018, 04:30 AM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,152

    Re: Conditional format diff cell, List with no Duplicate, & Report Monthly-Date to Date

    For 3.

    in B24

    =IFERROR(INDEX(Invoice[Date],SMALL(IF((Invoice[Partner]=Partner_Choice)*(Invoice[Month]=Month_Choice),ROW(Invoice[Partner])-ROW($A$4)+1,""),ROWS($1:1))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    copy down



    for 4

    =IF(Month_Choice<>"",IFERROR(INDEX(Invoice[Date],SMALL(IF((Invoice[Partner]=Partner_Choice)*(Invoice[Month]=Month_Choice),ROW(Invoice[Partner])-ROW($A$4)+1,""),ROWS($1:1))),""),IFERROR(INDEX(Invoice[Date],SMALL(IF((Invoice[Partner]=Partner_Choice)*(Invoice[Date]>=From_Choice)*(Invoice[Date]<=To_Choice),ROW(Invoice[Partner])-ROW($A$4)+1,""),ROWS($1:1))),""))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Change highlighted to required table headings.

    Change "," to ";" as required.
    Last edited by JohnTopley; 06-02-2018 at 04:46 AM.
    Reply With QuoteRegister To Reply
  3. 06-02-2018, 05:29 AM #3
    n4t4nes
    n4t4nes is offline
    Registered User
    Join Date
    12-04-2017
    Location
    Indonesia
    MS-Off Ver
    MS Office 2013
    Posts
    17
    Thank you so much John
    It's a great Formula
    Case 3 & 4 Solved
    Last edited by AliGW; 06-02-2018 at 05:30 AM. Reason: Unnecessary quotation removed.
    Reply With QuoteRegister To Reply
  4. 06-02-2018, 05:30 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Conditional format diff cell, List with no Duplicate, & Report Monthly-Date to Date

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  5. 06-02-2018, 12:17 PM #5
    n4t4nes
    n4t4nes is offline
    Registered User
    Join Date
    12-04-2017
    Location
    Indonesia
    MS-Off Ver
    MS Office 2013
    Posts
    17

    Re: Conditional format diff cell, List with no Duplicate, & Report Monthly-Date to Date

    Sorry AliGW,

    Once again, i apologize
    And thank you
    Last edited by n4t4nes; 06-02-2018 at 12:42 PM.
    Reply With QuoteRegister To Reply
  6. 06-08-2018, 09:43 AM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,535

    Re: Conditional format diff cell, List with no Duplicate, & Report Monthly-Date to Date

    As to case 1 I am guessing that you would like all of the cells associated with ID's 10 and 11 to highlight as there is no C Inc. in the Product[Partner] list. To accomplish this:
    1) Select A4:I14
    2) Use the following formula as a conditional formatting rule: =$I4=0
    As to case 2 use the following formula in cell R4 and copy down:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  7. 06-08-2018, 10:57 PM #7
    n4t4nes
    n4t4nes is offline
    Registered User
    Join Date
    12-04-2017
    Location
    Indonesia
    MS-Off Ver
    MS Office 2013
    Posts
    17

    Re: Conditional format diff cell, List with no Duplicate, & Report Monthly-Date to Date

    Quote Originally Posted by JeteMc View Post
    Let us know if you have any questions.
    Case 1 Solved
    Case 2 The Formula Worked Well, How to sort it by Alpabeth?

    Thank You JeTeMC
    Reply With QuoteRegister To Reply
  8. 06-09-2018, 09:58 AM #8
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,535

    Re: Conditional format diff cell, List with no Duplicate, & Report Monthly-Date to Date

    Try the following:
    1) Select cell R4,
    2) Paste the following formula into the formula bar:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    3) Simultaneously press the Ctrl, Shift and Enter keys,
    4) You should get a fx icon that will allow you to write the formula to all cells in the column (down to R11)
    Let us know if you have any questions.
    Reply With QuoteRegister To Reply
  9. 06-11-2018, 01:23 AM #9
    n4t4nes
    n4t4nes is offline
    Registered User
    Join Date
    12-04-2017
    Location
    Indonesia
    MS-Off Ver
    MS Office 2013
    Posts
    17

    Re: Conditional format diff cell, List with no Duplicate, & Report Monthly-Date to Date

    Thank You so much for solving all of my problem Everyone
    Reply With QuoteRegister To Reply
  10. 06-11-2018, 07:17 AM #10
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,535

    Re: Conditional format diff cell, List with no Duplicate, & Report Monthly-Date to Date

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional Format Highlighting a cell if the Date in the cell is before the current date
    By bluejule in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-25-2016, 12:27 PM
  2. Conditional format row if date + 7 is less than another cell date
    By jcmckeon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-28-2016, 01:14 PM
  3. Conditional Format for blanks & today date less than 42 days from another cell date
    By Tom Henderson in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2014, 10:13 AM
  4. Conditional formatting based on date - delete format if a date is entered in next cell
    By menstonbantam in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-23-2013, 06:21 AM
  5. Need to save Running Total and generate Weekly and Monthly Report based on date
    By soready42012 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-04-2012, 09:10 AM
  6. Format Result Of Date Diff To 2 Decimal Places
    By Reafidy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-27-2007, 04:59 PM
  7. [SOLVED] have a date cell pop up a monthly calendar to choose a date
    By lillywhite in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1