How to display #REF! (You need to connect these sheets) when using IFERROR to display Blank Cell-CodePudding

I understand that Google Sheets must be explicitly granted permission to pull data from other spreadsheets using IMPORTRANGE

However, when using IFERROR alongside VLOOKUP to display the value argument has a blank cell - How do you still allow for the #REF! (You need to connect these sheets) to be displayed?

I appreciate that as a workaround I could just display a "." but I'm curious if it's possible to legitimately without a workaround display a blank cell with no valid result but still allow for #REF! (You need to connect these sheets) to be displayed?

=IFERROR(VLOOKUP(Meta!$A$12,IMPORTRANGE(""&$M31&"","Template!$A$1:$H$6"),7, false), "")

CodePudding user response：

yes, they need to be connected and IFERROR will only hide that #REF! error. if you got complex (non-standalone IMPORTRANGE formula) that Allow access button will not appear. you are always obligated to run the IMPORTRANGE fx as standalone to establish the connection (it can be just a single cell - =IMPORTRANGE(""&$M31&"","Template!$A$1")) and only then use your complex (multi-layer) formula.

CodePudding user response：

This would decrease performance, probably by a lot, but I think this (or something similar) would technically work:

=if(1 0*importrange(...),iferror(vlookup(...)))