Formula to find and retrieve a specific value from a list with multiple values

Options
emka
emka
edited 04/01/24 in Formulas and Functions

Hi Community,

I am not sure what formula or combination of formulas to use for the below example:

I have a list of Spring products, each product is sorted into a type, and a similar list for Fall products. For each product I have a column to check if that specific product has an image. In a different sheet (here I use columns TYPE 2 & FORMULA?? but those should be on a different sheet), I have my list of types and I am looking for a formula that outputs the following: Look in the Spring table to see if any pant has an image, if it has an image output yes. If none of the Spring pants have images, look to see if any Fall pants have images. If any have an image, output yes, if none have images, output no.


Is there anything that I can use for that? Thanks in advance!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @emka

    Try this:

    =IF(OR(COUNTIFS({Source sheet Spring Type column}, Type@row, {Source Sheet Spring Image column}, "Yes") > 0, COUNTIFS({Source Sheet Fall Type}, Type@row, {Source Sheet Fall Image column}, "Yes") > 0), "YES", "NO")

    In case you are unfamiliar with 'cross sheet references' you will have to physically create references - you cannot simply copy paste this formula into your sheet. If you haven't created references before, see this link

    Will this work for you? If you need any help, just shout out

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @emka

    Try this:

    =IF(OR(COUNTIFS({Source sheet Spring Type column}, Type@row, {Source Sheet Spring Image column}, "Yes") > 0, COUNTIFS({Source Sheet Fall Type}, Type@row, {Source Sheet Fall Image column}, "Yes") > 0), "YES", "NO")

    In case you are unfamiliar with 'cross sheet references' you will have to physically create references - you cannot simply copy paste this formula into your sheet. If you haven't created references before, see this link

    Will this work for you? If you need any help, just shout out

    Kelly

  • emka
    Options

    Hi @Kelly Moore ,

    That is fantastic thank you so much! Worked like a CHARM and I was able to modify it to add 3 extra source sheets.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    @emka

    I'm glad that it worked for you and even happier that you further modified it to better suit your needs.

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!