Forum Discussion
Convert from Google Sheets using IFERROR not working
mtarler jenniferk007 I can't replicate the error on a Mac. As far as I can see there nothing special about any of the formulas in this tread that would cause an error on a Mac. All of the functions are supported in that version if I recall correctly. The only thing I can think of is that you get the message box line in the picture below.
As you know, this shows up on a PC as well if you are using the wrong list separator, for instance. If that's not it, can you jenniferk007 upload a screenshot showing us what exactly is going on. Even better if you could upload or share the file.
jenniferk007 In the attached I used this formula:
=LET(ls,FILTER(CourseTable,CourseTable[Change Request]="x",""), INDEX(ls,ROWS(ls),{1,3,4,10,11}))I formatted the data on the first tab as a table so the references can be cleaner (i.e. instead of a reference like '1. Outlining the Course'!$A$2:$K$100 which would be an issue if you exceeded line 100 later on or using '1. Outlining the Course'!$A:$K which could slow down the worksheet while it checks thousands of unused rows I use the reference CourseTable instead (the name I gave the table on the other tab).
but note that any formula will not pull the hyperlink or any other formatting over to the other sheet
it will also dynamically update if/when you change the "x" on the other sheet so that extra column for completion status WILL get out of synch if/when you add/remove "x"s from the other tab.
I have a list of standards on 1. Outlining the Course. On the Action Plan tab, I just want to list all the standards not met when an "x" is entered into the Change Request column.
Here is the Excel file, exported from Google Sheets.
jenniferk007 Going back to you original question and mtarler 's first response. Google Sheets and Excel are NOT the same. When you say that you open a Google sheets document in Excel, fair chance that Excel doesn't know what to do with it. Query, select and where are not an Excel functions, hence the "__xludf.DUMMYFUNCTION" part that Excel puts into the formula. You have to re-write it with valid Excel functions. I believe that's what Mat tried to do for you. Your Excel for Mac version 16.57 should support all the functions he used. But you can check that by entering =FILT or =SEQU. Excel should prompt the FILTER or SEQUENCE function. If not, then your version can't use Mat's formula.
- Just a s a reminder, the Google sheet code changed to the following in the Excel file:
=IFERROR(__xludf.DUMMYFUNCTION("IFERROR(Query('1. Outlining the Course'!A2:K26,""Select A,C,D,J,K where G <>'' "",1),""No records found"")"),"Standard") When I downloaded the Google sheet and opened with Excel, a prompt asked if I wanted to repair. Then BBEdit opened a text file with the following code. There may be a clue if you can distill the contents:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">< logFileName>Repair Result to File_Name.xml</logFileName><summary>Errors were detected in file 'File_Name.xlsx'</summary><removedFeatures summary="Following is a list of removed features:"><removedFeature>Removed Feature: Data validation from /xl/worksheets/sheet2.xml part</removedFeature><removedFeature>Removed Feature: Data validation from /xl/worksheets/sheet3.xml part</removedFeature><removedFeature>Removed Feature: Data validation from /xl/worksheets/sheet8.xml part</removedFeature></removedFeatures></recoveryLog>
