SOLVED

Unexpected error with LET function

%3CLINGO-SUB%20id%3D%22lingo-sub-1531431%22%20slang%3D%22en-US%22%3EUnexpected%20error%20with%20LET%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531431%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20having%20a%20problem%20with%20the%20new%20function%20LET.%3CBR%20%2F%3EI%20created%20a%20formula%20that%20writes%20down%20number's%20values%20to%20letters.%20For%20this%2C%20I%20assigned%2040%20names%20to%20formulas%20in%20the%20function.%20Initially%2C%20it%20worked%20perfectly%2C%20but%20whenever%20I%20copy%20the%20formula%20(in%20current%20or%20a%20new%20workbook)%2C%20close%20and%20reopen%20the%20file%2C%20it's%20triggering%20an%20error.%20The%20formula%20disappears%20and%20leaves%20only%20the%20last%20calculated%20result%20in%20the%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20knows%20what%20happened%2C%20I'd%20appreciate%20any%20tip.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERicardo%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22error1.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206259i4BE77FB911911602%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22error1.jpg%22%20alt%3D%22error1.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22error2.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206258i2E2AFB25DE0D05F8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22error2.jpg%22%20alt%3D%22error2.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1531431%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531909%22%20slang%3D%22en-US%22%3ERe%3A%20Unexpected%20error%20with%20LET%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531909%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F732873%22%20target%3D%22_blank%22%3E%40noguerar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELooks%20like%20a%20bug%2C%20I'd%20suggest%20you%20to%20send%20a%20frown%20through%20File-%26gt%3BFeedback.%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20number%20to%20text%20formula%20with%20LET()%20you%20may%20find%20in%20discussion%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fannouncing-let%2Fba-p%2F1233572%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fannouncing-let%2Fba-p%2F1233572%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1604132%22%20slang%3D%22en-US%22%3ERe%3A%20Unexpected%20error%20with%20LET%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1604132%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3A%3C%2FP%3E%3CP%3EThanks%20for%20your%20reply!%3C%2FP%3E%3CP%3EI%20went%20through%20the%20number%20to%20letter%20formula%20in%20the%20discussion%20and%20found%20it%20very%20helpful!%3C%2FP%3E%3CP%3EI%20rewrote%20mine%20(for%20Spanish%20numbers)%20in%20a%20similar%20way%2C%20but%20I%20keep%20getting%20the%20same%20error.%3C%2FP%3E%3CP%3EI%20already%20wrote%20to%20Excel%20Feedback%20and%20I%20am%20still%20waiting%20for%20their%20response.%3C%2FP%3E%3CP%3ESad%20not%20to%20be%20able%20to%20enjoy%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1605881%22%20slang%3D%22en-US%22%3ERe%3A%20Unexpected%20error%20with%20LET%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1605881%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F732873%22%20target%3D%22_blank%22%3E%40noguerar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20submit%20the%20file%20where%20this%20function%20returns%20an%20error%2C%20it'll%20be%20easier%20to%20check%20what%20is%20wrong.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20only%20point%2C%20from%20the%20time%20of%20publishing%20abovementioned%20sample%20of%20the%20forum%20there%20was%20introduced%20the%20change%20in%20function%20-%20it%20doesn't%20support%20dots%20within%20names%20any%20more.%20Other%20words%2C%20with%20name%20as%20H.txt%20you'll%20have%20an%20error%2C%20use%20Htxt%20instead%20or%20any%20other%20name%20without%20the%20dot.%20Correct%20formula%20is%20in%20attached%20file.%20But%20that's%20another%20issue%2C%20not%20as%20in%20initial%20post.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606052%22%20slang%3D%22en-US%22%3ERe%3A%20Unexpected%20error%20with%20LET%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606052%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20tried%20to%20open%20the%20file%20you%20sent%20but%20I%20get%20the%20same%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20attaching%20my%20file%2C%20so%20you%20might%20want%20to%20check%20it%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22noguerar_0-1598132189068.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214106i57F72B0724D29D41%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22noguerar_0-1598132189068.png%22%20alt%3D%22noguerar_0-1598132189068.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606072%22%20slang%3D%22en-US%22%3ERe%3A%20Unexpected%20error%20with%20LET%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F732873%22%20target%3D%22_blank%22%3E%40noguerar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you.%20Your%20formula%20disappears%20if%20open%20the%20file%2C%20but%20I%20was%20able%20to%20reproduce%20the%20issue.%20Will%20try%20to%20play%20with%20initial%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1613065%22%20slang%3D%22en-US%22%3ERe%3A%20Unexpected%20error%20with%20LET%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1613065%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F732873%22%20target%3D%22_blank%22%3E%40noguerar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20your%20information%2C%20I%20also%20sent%20a%20frown.%20People%20at%20Microsoft%20can%20reproduce%20the%20bug%2C%20hope%20will%20be%20fixed%20one%20day.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20note%20this%20function%20is%20in%20Beta%20mode%2C%20that%20means%20bugs%20could%20appear%20in%20some%20scenarios%2C%20plus%20function%20behaviour%20on%20time%20of%20general%20availability%20could%20be%20slightly%20different%20from%20what%20we%20have%20now.%20That's%20normal%20process%2C%20so%20far%20we%20use%20the%20function%20(as%20any%20other%20beta%20ones)%20on%20our%20own%20risk%2C%20even%20if%20such%20risk%20is%20relatively%20small.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1614088%22%20slang%3D%22en-US%22%3ERe%3A%20Unexpected%20error%20with%20LET%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1614088%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%2C%20I%20hope%20they%20can%20find%20what's%20going%20on.%20I%20know%20there%20is%20a%20risk%2C%20but%20that's%20why%20we%20are%20Insiders%2C%20to%20help%20out%20looking%20for%20issues%20like%20this%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi everyone:

 

I'm having a problem with the new function LET.
I created a formula that writes down number's values to letters. For this, I assigned 40 names to formulas in the function. Initially, it worked perfectly, but whenever I copy the formula (in current or a new workbook), close and reopen the file, it's triggering an error. The formula disappears and leaves only the last calculated result in the cell.

 

If anyone knows what happened, I'd appreciate any tip.

 

Kind regards,

 

Ricardo

error1.jpgerror2.jpg

7 Replies
Highlighted

@noguerar 

Looks like a bug, I'd suggest you to send a frown through File->Feedback.

By the way, number to text formula with LET() you may find in discussion here https://techcommunity.microsoft.com/t5/excel-blog/announcing-let/ba-p/1233572

Highlighted

Hi @Sergei Baklan:

Thanks for your reply!

I went through the number to letter formula in the discussion and found it very helpful!

I rewrote mine (for Spanish numbers) in a similar way, but I keep getting the same error.

I already wrote to Excel Feedback and I am still waiting for their response.

Sad not to be able to enjoy the formula.

Highlighted

@noguerar 

Perhaps you may submit the file where this function returns an error, it'll be easier to check what is wrong.

 

The only point, from the time of publishing abovementioned sample of the forum there was introduced the change in function - it doesn't support dots within names any more. Other words, with name as H.txt you'll have an error, use Htxt instead or any other name without the dot. Correct formula is in attached file. But that's another issue, not as in initial post.

Highlighted

Hi, @Sergei Baklan:

 

Just tried to open the file you sent but I get the same error.

 

I'm attaching my file, so you might want to check it out.

 

Thank you!

noguerar_0-1598132189068.png

 

Highlighted

@noguerar 

Thank you. Your formula disappears if open the file, but I was able to reproduce the issue. Will try to play with initial formula.

Highlighted
Best Response confirmed by cuong (Microsoft)
Solution

@noguerar 

For your information, I also sent a frown. People at Microsoft can reproduce the bug, hope will be fixed one day.

 

Please note this function is in Beta mode, that means bugs could appear in some scenarios, plus function behaviour on time of general availability could be slightly different from what we have now. That's normal process, so far we use the function (as any other beta ones) on our own risk, even if such risk is relatively small.

Highlighted

@Sergei Baklan 

 

Thank you very much, I hope they can find what's going on. I know there is a risk, but that's why we are Insiders, to help out looking for issues like this one.