I have finally figured out the issue. This is typically a case when you have applied a *sort* in the Filter View. If your sheet has formula's that refer to the ImportRange, the sorting will cause issues and the row mapping is changed. For Example consider I have a Sheet 1 with import Range and I have another sheet "Sheet2" which has formula's that refer to the importRange output in Sheet1. In this case if I create a filter view in Sheet2 an apply sort in the filter view you will get this error in all the cells that have formula referring to Sheet1.
*Solution:* Unfortunately, there is no way to visually identify which column is the sort applied to. The only way to solve this(which I have identified) is to delete the existing filter and create a new filter. *Note*: I have not found a solution to clear the sort in Google Sheet but Excel allows us to clear any sort that is applied. Refer the following link to clear a sort in Excel: https://www.sheetzoom.com/Tips/how-to-revert-back-to-original-sort On Tuesday, May 20, 2014 at 6:47:49 PM UTC+5:30, Emil Bryngelsson wrote: > > I have a google sheets spreadsheet that makes use of importrange to > display data from another sheet in the same document. > > The formula for using importrange is this > > =IMPORTRANGE(M1;"Datasheet!G2:G300") > > The cell M1 contains the URL to the document itself, and the reason I put > it in one cell is that I'm reusing that same URL in many columns, and it is > more readable to not have the whole URL in each formula. > > > However, when activating a filter view, I get an REF# ERROR for example > *Error:* The formula in cell M1 is referencing a relative range outside > the filter. > > > And nothing is displayed. > > It works fine when not having an active filter view. > > It ALSO WORKS FINE when the URL is used directly in the importrange > formula, instead of using M1 and having the URL in cell M1. > > What is the difference and why does this happen? These cases should be > identical. It's a ridiculous error. > > -- You received this message because you are subscribed to the Google Groups "Google Spreadsheets API" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/google-spreadsheets-api/cbc1cf1b-16b4-4b2e-9b0a-2fa92b43afd3%40googlegroups.com.
