I have a google doc spreadsheet, where I calculate name of the weekday based on date. I do it with formula:
=SWITCH(WEEKDAY(B3),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5, "Thursday",6,"Friday",7,"Saturday")
So the spreadsheet looks like:
I would like to export it into Excel and then send it via outlook and possibly open in WEB Excel app. After Exporting and opening in a WEB Excel, I see that the formula is prepended with a extra @
:
=@SWITCH(WEEKDAY(B3),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5, "Thursday",6,"Friday",7,"Saturday")
Which I thought was the cause of #NAME?
error, which is shown in web based outlook and web based excel:
I would like open file in a based WEB Excel app.
UPDATE Following advice from the comments I have unzipped the file and checked the worksheets/sheet.xml. The xml contains the rule without the "@":
<f t="shared" ref="A3:A31" si="1">SWITCH(WEEKDAY(B3),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5, "Thursday",6,"Friday",7,"Saturday")</f>
So, @
may not be the reason for #NAME?
error. How to fix this #NAME?
error with this formula, if it is not created by @
?
Disclaimer: this is just a workaround to OPs situation & not an intended solution that solves the @
character that gets prepended to switch()
when uploaded to excel-web
An alternative formula (to OPs switch()
) which somehow retains ONLY the data (formula vanishes) when downloaded as .xlsx file and uploaded to excel-web
={"header";
index(if(len(B3:B),xlookup(weekday(B3:B),sequence(7),text(sequence(7),"dddd"),),))}