Convert from Google Sheets using IFERROR not working

Occasional Contributor

Asking the experts why a formula working in Google Sheets fails to work in an Excel file that has been downloaded? Here is the formula working in Sheets:

 

=IFERROR(Query('TAB1'!A2:K,"Select A,C,D,J,K where G <>'' ",1),"No records found")


Does Excel require something different in the syntax?

 

The formula looks for a filled in cell in G column in TAB1, then transfers the entire row of data into TAB2 where the formula is hiding. This works flawlessly in Google Sheets. However, it does not work as it is shown in the downloaded Excel file.

 

When I open the downloaded file with Excel, I see this message "We found a problem with some content in 'Your_File.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

 

Also the formula in the downloaded Excel version has changed to this:

 

=IFERROR(__xludf.DUMMYFUNCTION("IFERROR(Query('TAB1'!A2:K26,""Select A,C,D,J,K where G <>'' "",1),""No records found"")"),"Standard")

14 Replies

@jenniferk007 

Possibly (You may adjust the ranges to accommodate your data):

=LET(f,FILTER('TAB1'!A2:K5,'TAB1'!G2:G5<>" "),INDEX(f,SEQUENCE(ROWS(f)),{1,3,4,10,11}))

 

basically google sheet and MS excel are not the same. QUERY doesn't exist in MS excel. Based on the context I would try something like:
=FILTER('TAB1'!A2:K26, 'TAB1'!G2:G26<>"", "No records found")
That will return all the columns but if you need only columns A,C,D,J,K then try:
=FILTER(INDEX('TAB1'!A2:K26,SEQUENCE(25,1,2),{1,3,4,10,11}), 'TAB1'!G2:G26<>"", "No records found")

Thank you for the reply, but I am getting a "The function isn't valid"error message on the following... the inner parenthesis are lighting up, the set inside of INDEX.

=FILTER(INDEX('TAB1'!A2:K26,SEQUENCE(25,1,2),{1,3,4,10,11}), 'TAB1'!G2:G26<>"", "No records found")

@jenniferk007 I'm not sure what is the issue.  I did find a small error but that just caused it to miss a line not give the error you mentioned.  here is an example in the attached.  I also updated it to use LET() and set the "in" range and sequence automatically on that.  but picking the columns {1,2, ...} on line 4 and the column to check on line 5 is still manual:

=LET(in,A2:G16,
     FILTER(INDEX(in,
                  SEQUENCE(ROWS(in)),
                  {1,3,4,6,7}),
             G2:G16<>"",
             "No records found"))

I should ask, do you have Excel 365?  If you type =FIL do you see FILTER in the pop up suggestion window?

Can you attach a sample file and maybe a screenshot of the error.

Still in using this code, I get the "That function isn't valid" error:

 

=LET(in,A2:G16,FILTER(INDEX(in,SEQUENCE(ROWS(in)),{1,3,4,6,7}),G2:G16<>"","No records found"))

 

@mtarler 

What version of Excel are you using?
I am using Excel 16.57 on a Macbook.
I'm not on the Mac. @Riny_van_Eekelen can you help identify is that version has the new functionality?

@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.

Screenshot 2022-08-02 at 05.53.24.png

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.

@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>

 

 

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")

@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 

 

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  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.