Excel to Smartsheet formula conversion error

vaibhavtolani

SAFe PI Calendar.xlsx

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

Anupriya

& 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

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

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.

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