Collect date on sheet 2 if tag on sheet 1 is contained within tag column on sheet 2

Hi, I'm stumped on a formula and hoping to get some help.

I have 2 sheets; Sheet 1 is titled E&I and contains the tag number and Sheet 2 is titled Open PO's and contains the date the item is due and the tag number with additional characters in a single cell. I am trying to pull in the date from the Open PO report into my E&I report if the tag number is contained within the column.

I've tried multiple formulas but this is what I currently have in Sheet 1: =IF(CONTAINS([Tag No]@row, {Open PO's Partno}), COLLECT({Open PO's Due Date}, {Open PO's Partno}, [Tag No]@row), "Received")


Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    In that case we need to switch over to an INDEX/COLLECT so we can work in a CONTAINS function. How does this work for you:

    =IFERROR(INDEX(COLLECT({Open PO's Due Date}, {Open PO's Partno}, CONTAINS([Tag No]@row, @cell)), 1), "Received")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try an INDEX/MATCH instead.

    =IFERROR(INDEX({Date Column}, MATCH({Number Column}, [Tag No]@row, 0)), "Received")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • amber.lange
    amber.lange ✭✭✭✭

    Hi Paul, that gave me an incorrect argument error. I was thinking the formula needed to use "contains" because the tag number (column Fpartno) on the sheet with the due dates also includes a prefix for the supplier. I tried using CONTAINS in your formula suggestion but that didn't work either. Below are my sheet references if that's helpful.

    Thank you for your assistance.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Sorry. I do that every time. Switch the cross sheet reference and cell reference within the MATCH function.


    I have

    .....MATCH({Range}, column@row, .....


    But it should be

    .....MATCH(column@row, {Range}, .....

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • amber.lange
    amber.lange ✭✭✭✭

    Paul,

    It's still giving the incorrect argument error.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. Ok. Parenthesis are a little off, and you're going to want that zero there at the end of the MATCH function. See below.


    =INDEX(.....MATCH(....., ....., 0)), "Received")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • amber.lange
    amber.lange ✭✭✭✭

    Hi @Paul Newcome. This is the formula I have, which I think matches what you suggested, but it tells me everything is received, which is incorrect.

    =IFERROR(INDEX({Open PO's Due Date}, MATCH([Tag No]@row, {Open PO's Partno}, 0)), "Received")

    I tried manipulating the formula and got some dates returned, but they were not the correct dates, nor do I know where those dates came from since they aren't in the data source.

    =IFERROR(INDEX({Open PO's Due Date}, MATCH([Tag No]@row, {Open PO's Partno})), "Received")

    Is it something with the MATCH function? The tag numbers do not 100% match between the sheets because one sheet includes the vendor prefix, the other does not.

    I'm sorry for all the back and forth but appreciate your help.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide screenshots for both? I didn't realize it would not be an exact match due to additional characters within the string.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • amber.lange
    amber.lange ✭✭✭✭

    I thought I mentioned that, my apologies. Below are screenshots. It's not consistent on the naming convention prefex in the Fpartno column. Some people put a comma after the vendor and on PUMP-110 it says "options" after the tag number because we cannot have duplicates within a job, if that makes sense. Overall, the "Tag No" column will 99% of the time be contained within "Fpartno".



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    In that case we need to switch over to an INDEX/COLLECT so we can work in a CONTAINS function. How does this work for you:

    =IFERROR(INDEX(COLLECT({Open PO's Due Date}, {Open PO's Partno}, CONTAINS([Tag No]@row, @cell)), 1), "Received")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • amber.lange
    amber.lange ✭✭✭✭

    That works! Thank you for all your time on this; it will be very helpful going forward.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!