Dropdown.. Not in List ?

Options

Is there any way to find out the 63 "Vendors" in my sheet that is not in the dropdown for Vendors?

The only way I can figure it out is to create a Pivot for a count of the column and then compare to my dropdown list. 🤔

Best Answer

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @bcwilson.ca

    Maybe you could copy the dropdown values and add two so-called helper columns and use FIND to see if the value is included or not.

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • bcwilson.ca
    bcwilson.ca ✭✭✭✭✭
    edited 01/20/21
    Options

    That's what I did...

    I pasted the dropdowns in a separate file then created a COUNTIF and counted the occurrences of the Vendor in the Vendor file using the Vendor in the sheet as a match..

    =IF(COUNTIF({Vendor Master VendorName}, [Assigned Vendor]@row) > 0, 0, 1).. Now I get a RED Flag if the vendor is not in the Vendor Master File

    So if it is Zero It is not in my drop down and it gets a red flag

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @bcwilson.ca

    Excellent!

    Thanks for sharing!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • bcwilson.ca
    bcwilson.ca ✭✭✭✭✭
    Options

    @Andrée Starå

    I went a bit further and made the vendor file permanent..

    Then I created a report based on things like the vendor status and the locations they service...

    so filtering out the ones we do not do business with anymore and those that cannot service our out of city locations.

    Then I run the report and copy the names to the clipboard and paste them in the dropdown..

    Manual intensive but it works and if you set up a procedure then they are all maintained fairly easily

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!