How do I display my function in Sheet2 where data in another Sheet1, in google spreadsheet?-CodePudding

Courtesy :Jean-Pierre Verhulst

In my below GOOGLE SHEETS formulae , my data (Column A ) is in Sheet1, how do i change so that it displays the result sequence in Sheet2 ?

=filter(row(indirect("A"&A1&":A"& sort(A1:A, 1, 0))), isna(match(row(indirect("A"&A1&":A"& sort(A1:A, 1, 0))), A1:A,0)))

https://support.google.com/docs/thread/5868559/identify-missing-numbers-in-a-sequence?hl=en

CodePudding user response：

I'd personally avoid volatile INDIRECT set-ups, even though I understand that that function is volatile only in Excel, and apparently not in Sheets.

=FILTER(SEQUENCE(MAX(Sheet1!A:A)-MIN(Sheet1!A:A) 1,1,MIN(Sheet1!A:A)),ISNA(MATCH(SEQUENCE(MAX(Sheet1!A:A)-MIN(Sheet1!A:A) 1,1,MIN(Sheet1!A:A)),Sheet1!A:A,0)))