+ Reply to Thread
Results 1 to 5 of 5

Conditional formatting a pick list

  1. #1
    Registered User
    Join Date
    04-19-2020
    Location
    Michigan,USA
    MS-Off Ver
    Office 365
    Posts
    2

    Conditional formatting a pick list

    I’m new to excel, so please bear with me.
    I work in a food distribution warehouse, and my company uses an excel sheet that displays the picks per order for a given department. I want to use this spreadsheet to display where each department is in the days work. We use a voice pick program that will show me what status the order is (complete, in use, available,etc.), and I can export this data to a new excel file. How do I go about using this exported data to conditional format my spreadsheet? If possible I would like to use grayscale colors of the cell to determine the status of the order (white= available, light gray=in use, dark gray=complete)

    On the spreadsheet, the order numbers are in column A, starting from top to bottom of the sheet. The department names go across row 1, and each cell at the cross section (the area I'd like to format) shows the picks for that order, for that department.

    The exported file can be arranged however, but for ease I’ll make it order number in column A, department in column B, and status in column C.

    Hopefully I explained well enough, and used the correct terms! Thanks to anyone who can help, as I’ve googled for weeks, but can’t seem to find a similar problem.

    In the attached file, I used different sheets, but in practice this would have to be done using linked files.
    Attached Files Attached Files
    Last edited by TheEyeofIris; 04-19-2020 at 03:42 PM.

  2. #2
    Registered User
    Join Date
    04-04-2020
    Location
    Virginia
    MS-Off Ver
    Office 365.
    Posts
    1

    Re: Conditional formatting a pick list

    The first thing you need to do is highlight the entire range of cells and convert it into a table (just highlight them, click top left, shift+click the lower right, Insert > Table).

    From there, you can conditionally format a range of data. I'm not sure where in the ribbon to find it (just spent 5 minutes looking for it), but if you're using Excel 2016 or later, use the search box at the top to find conditional formatting. Within the conditional formatting tool, you'll be able to set your thresholds and values by color.

    I hope this helps.
    Last edited by Mr.Cheeseball; 04-19-2020 at 04:25 PM.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional formatting a pick list

    No real need to convert to a structured table, but you can if you want to (sorry Mr.Cheeseball)

    1. highlight the range you want to apply the conditional formatting to (B3:D20 in your example)
    2. on the home tab, styles, select CF
    3. select new rule, select use formula
    4. enter =INDEX('Exported file'!$C$1:$C$54,MATCH(spreadsheet!$A3&spreadsheet!B$1,INDEX('Exported file'!$A$1:$A$54&'Exported file'!$B$1:$B$54,0),0))="Complete"
    format fill dark Grey

    Repeat 3 & 4 with
    =INDEX('Exported file'!$C$1:$C$54,MATCH(spreadsheet!$A3&spreadsheet!B$1,INDEX('Exported file'!$A$1:$A$54&'Exported file'!$B$1:$B$54,0),0))="In Use"

    You dont really need a rule for the 3rd type, it will default to white if the other 2 are not met
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-19-2020
    Location
    Michigan,USA
    MS-Off Ver
    Office 365
    Posts
    2

    Talking Re: Conditional formatting a pick list

    Yes, this is exactly it! Thank you! I was going crazy trying to think of what formula to use, I will definitely dive into index and match to learn how to use this in the future!

    On another note, how do I mark this thread as solved?
    Edit: found the solved button

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional formatting a pick list

    Happy to help and thanks for the feedback

+ 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. Replies: 3
    Last Post: 12-08-2016, 10:37 AM
  2. Problem getting conditional formatted dropdown list items to keep their formatting in list
    By scottatbuckman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2014, 02:39 PM
  3. Pick diff pick list as per column value
    By ROHAN999 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-10-2013, 12:10 PM
  4. [SOLVED] Conditional Formatting of List based on selections from Dropdown list
    By FoxyPinkChick in forum Excel General
    Replies: 4
    Last Post: 05-31-2013, 03:32 AM
  5. A pick list that defines another pick list
    By aaanenson in forum Excel General
    Replies: 1
    Last Post: 09-21-2012, 10:48 AM
  6. Excel 2007 : Pick-List pick fills in another cell
    By hpasso217 in forum Excel General
    Replies: 0
    Last Post: 02-28-2011, 05:38 PM
  7. Replies: 1
    Last Post: 06-27-2005, 07: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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1