Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Best Formula to use for Validation between 2 Columns in 2 Sheets

Good Day,

Looking for some ideas/suggestions for a formula to use to verify values placed in a column in a sheet. In my scenario, I have Sheet 1 and Sheet 2. Within this 2 sheets are 2 columns that need to match. Sheet 1 - Column: 'Product Category' is a manually entered field. This column/field needs to match or flag when it is not matching Sheet 2 - Column: 'product_category', Sheet 2 is essentially a lookup file to assist with data processing with performance data on the backend. So that is why We need a validation essentially that says that we only have current live product categories in sheet 1 and that flags anything that is not on that product look up sheet (sheet 2).

Sheet 1 - Product Category

Sheet 2 - Product_Category

Answers

  • ✭✭✭✭✭

    Another layer to this…. some fields entered in sheet 1 are multiple products separated by a |, however in sheet two they are there own row so I might need a helper column or include the 'has' function if possible

    Sheet 1

    Sheet 2 does not have | but the products will be their own row

  • Community Champion

    Hi @ConnorForm

    As shown in the image below, you can create a range for the HAS function by substituting "|" with CHAR(10) in a multi-select dropdown helper column, like PC in this example.

    Site faviconSmartsheet

    https://app.smartsheet.com/b/publish?EQBCT=40a92a13e4a24f3da0ce4d767d5936d3

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6