Excel to Smartsheet formula conversion error

Options

Hello All,


I am trying to import Scaled Agile Team Event calendar excel onto Smartsheet.


I am facing difficulty in automating Column F - Team Events on Smartsheet.


If someone can help me debug the query to work in Smartsheet.


I am attaching the excel from Scaled Agile and also pasting the query down here.


Excel Column Team Events - F12 (Working Query)


="Iteration " & LOOKUP(2,1/($C$21:$C$27<=G12)/($D$21:$D$27>=G12),$B$21:$B$27)&"."&NUMBERVALUE((G12-(LOOKUP(2,1/($C$21:$C$27<=G12)/($D$21:$D$27>=G12),$C$21:$C$27)))/(7*$B$7)+1)&IF((NUMBERVALUE((G12-(LOOKUP(2,1/($C$21:$C$27<=G12)/($D$21:$D$27>=G12),$C$21:$C$27)))/($B$7*7)+1))=$B$6," IP","")


Smartsheet Import Adapted Query (Not working / #UNPARSEABLE) -


="Iteration "&LOOKUP(2,1/($[Column3]$20:$[Column3]$26<=[Column7]17)/($[Column4]$20:$[Column4]$26>=[Column7]17),$[Column2]$20:$[Column2]$26)&"."&_XLFN.NUMBERVALUE(([Column7]17-(LOOKUP(2,1/($[Column3]$20:$[Column3]$26<=[Column7]17)/($[Column4]$20:$[Column4]$26>=[Column7]17),$[Column3]$20:$[Column3]$26)))/(7*$[Column2]$6)+1)&IF((_XLFN.NUMBERVALUE(([Column7]17-(LOOKUP(2,1/($[Column3]$20:$[Column3]$26<=[Column7]17)/($[Column4]$20:$[Column4]$26>=[Column7]17),$[Column3]$20:$[Column3]$26)))/($[Column2]$6*7)+1))=$[Column2]$5," IP","")


Thanks in advance

Answers

  • Anupriya
    Anupriya ✭✭✭✭
    Options

    & doesn't work for concatenation in Smartsheet. Use + instead. Also, Smartsheet doesn't have a LOOKUP function. You can try VLOOKUP instead or INDEX-MATCH.

    Functions List | Smartsheet Learning Center

  • vaibhavtolani
    Options

    Hey Anupriya,

    Thanks for your response.

    Its gives me an error "Invalid Operation" when I run the below query based on your inputs


    ="Iteration " + VLOOKUP(2, 1 / ([Column3]20:[Column3]26 <= [Column7]11) / ([Column4]20:[Column4]26 >= [Column7]11), [Column2]20:[Column2]26) + "." + VALUE(([Column7]11 - (VLOOKUP(2, 1 / ([Column3]20:[Column3]26 <= [Column7]11) / ([Column4]20:[Column4]26 >= [Column7]11), [Column3]20:[Column3]26))) / (7 * [Column2]6) + 1) + IF((VALUE(([Column7]11 - (VLOOKUP(2, 1 / ([Column3]20:[Column3]26 <= [Column7]11) / ([Column4]20:[Column4]26 >= [Column7]11), [Column3]20:[Column3]26))) / ([Column2]6 * 7) + 1)) = [Column2]5, " IP", "")


    Is there something I am missing ?

  • vaibhavtolani
    Options

    Index Match Example -

    =INDEX([Column]:[Column], MATCH(DATE(YYYY, DD, MM), [Column]:[Column]))

    How do I embed this in my current formula to work ?

  • Genevieve P.
    Options

    Hi @vaibhavtolani

    Smartsheet and Excel have different functions and formula structures; you won't be able to copy/paste a formula from one type of sheet into the other, or replace one function with another. The formula will need to be re-created and re-written in Smartsheet.

    That said, I'm not quite sure I understand what it is your formula is looking to do. Would you mind explaining the calculation that you're looking to do in a written description?

    (As a side-note, it looks like Anupriya didn't see your response. You may want to @mention members in Community if you want to send them a notification).

    Thanks!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!