Excel to Smartsheet formula conversion error
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
-
& 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.
-
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 ?
-
Index Match Example -
=INDEX([Column]:[Column], MATCH(DATE(YYYY, DD, MM), [Column]:[Column]))
How do I embed this in my current formula to work ?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!