Formula to Read and Update from Other Columns

Hi All,

I'm stumped on a particularly complicated Formula that reads multiple columns from a Sheet that not only displays the text needed, but will also be updated as the corresponding columns are updated.


The formula in Excel seems to simply be =' which i presume displays the text required. Below i have added the formula being used along with the display so you can see both parts. I have tried to import the Document directly into Smartsheet with the formula, however I receive 'UNPARSEABLE' as a response which I'm fearing may mean I cannot duplicate this entry.

Any help would be greatly appreciated!


Excel Formula ="3DOCSBAHK1/P/"&H172&"/"&I172&"/"&H172&"/"&TEXT(F172,"DDMMMYY")&"/"&G172&"/"&TEXT(J172,"DDMMMYY")&"/"&C172&"/"&E172&"/"&D172&"-1"


Excel Response


Many Thanks

Matt

Best Answer

Answers

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

    Smartsheet "adds" bits together instead of using "and".


    ="3DOCSBAHK1/P/" + H@row + "/" + I@row.................................

    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

  • Matt Wood
    Matt Wood ✭✭✭

    Thanks Paul, that's done the trick! Thank so much for taking the time to reply.

  • 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

  • Matt Wood
    Matt Wood ✭✭✭

    Sorry Paul, another question on the same formula. I've got that formula working perfectly in my sheet:

    ="3DOCSBAHK1/P/" + Nationality@row + "/" + Passport@row + "/" + Nationality@row + "/" + DOB@row + "/" + Gender@row + "/" + [Passport Expiry Date]@row + "/" + [Passport Family Name]@row + "/" + [Passport First Name]@row + "/" + [Passport Middle Name]@row + "-1"


    3DOCSBAHK1/P/GBR/551648655/GBR/07/02/83/F/05/10/28/BINGHAM/RIKKI/LOUISE-1


    However I need to complicate it even further! Essentially we're using this to create the entry required to get it into our system.

    It won't recognise the Date Format unless it reads as DDMMMYY. This isn't an option within the date options in Smartsheet and in our Excel sheet, we have an =TEXT formula to convert the date into the required DDMMMYY format. Looking around the forums it looks like an =INDEX formula but it seems really complicated when it could just be an =TEXT formula.

    A) Is there a way we can do this

    B) How can we get the formula (if it exists) into the middle of the formula we already have?


    Many Thanks

    Matt

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'll provide an example for DOB@row here. You would just need to replicate the logic for the [Passport Expiry Date] portion.


    ="3DOCSBAHK1/P/" + Nationality@row + "/" + Passport@row + "/" + Nationality@row + "/" + IF(DAY(DOB@row) < 10, "0") + DAY(DOB@row) + "" + IF(MONTH(DOB@row) < 10, "0") + MONTH(DOB@row) + "" + RIGHT(YEAR(DOB@row), 2) + "/" + Gender@row + "/" + [Passport Expiry Date]@row + "/" + [Passport Family Name]@row + "/" + [Passport First Name]@row + "/" + [Passport Middle Name]@row + "-1"

    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

  • Matt Wood
    Matt Wood ✭✭✭

    Thanks again Paul. Just the job. Again, many thanks for taking the time to respond

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Always 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!