Convert from Google Sheets using IFERROR not working

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.  

Riny_van_Eekelen 

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.

Riny_van_Eekelen 

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>

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.