Formula "#NAME?" error on export to .xlsx
AnsweredI have a Workiva spreadsheet with formulas linked to other worksheets in the same spreadsheet workbook. One of these formulas is an index/match lookup formula inside an "ifna" wrapper. The function works in Workiva, but when I export to Excel and click "enable editing" all these functions produce the #NAME? error in the exported .xlsx file. I can clear the errors in the Excel file by hitting F2 on each cell where the formula appears and hitting enter (essentially just clicking in and out of each cell), but I am not sure why these would export with an error message?
Is there a way to export these formulas so there is no formula error?
Is there a way to export these formulas so there is no formula error?
0
-
I have had that same problem with other formulas and I found out it is a formatting issue, it would convert the cells into a text value rather than a number value. In the cell(s) that are having the error, may want to add in the formula =NUMBERVALUE( and in the export it will export it as a number (if that is what you are after) and this should elimintate the #NAME error on the export. 0
Thanks good idea, but my formula returns a text value so this would return an error, also Workiva doesn't seem to recognize the =NUMBERVALUE formula so creates an error in Workiva? 0
Try using the =TRIM( formula this removes all extra characters except spaces between words and kicks it out as text.
I have used the NUMBERVALUE in Wdesk, you have to make sure you define what the number looks like. for example:F
=NUMBERVALUE(C5,$###,##0). This will convert cell C5 into a number that is formatted like $100,0000
Looks like the issue goes away if I remove the IFNA formula -- I can add the TRIM function as well but if the IFNA formula is present it doesn't help. Not sure what it is about this formula that Excel doesn't like as the spelling for the formula is the same in Workiva and Excel but I'll remove it. Thanks anyway! 0Please sign in to leave a comment.
Comments
4 comments