Show results of a FILTER() function in non-continuous rows

[omortari]

Say I have a document with 2 sheets.
Sheet 1 looks like this [see IMAGE 1]

Column A shows numbers (ascending order) and column B shows dates.
Sheet 2 looks like this [IMAGE 2]

On A1 of Sheet 2 I want to use FILTER() to fetch rows from Sheet 1 if they match a criteria (say, if the number inside cell from column A > 5).

So the function I wanna write in A1 of Sheet 2 is:
=FILTER($'Sheet 1'.A:B; $'Sheet 1'.A:A>5; "")
so it fetches the rows from both columns, no matter how long Sheet 1 is.

But here’s the catch: if I just type that and hit Enter, some results will overwrite the red cells that say “SKIP THESE CELLS CONTINUE BELOW”. I need the results to be shown like this [see IMAGE 3]

and not like this [see IMAGE 4]

How the hell do I go about doing that? Is that even possible?

(Sorry for the “see image x” stuff, had to do it since this stupid forum doesn’t allow me to attach more than 1 media since I just signed in)

[Villeroy]

just an idea:
ask121037.ods (33.5 KB)

[omortari]

That doesn’t really work cause the panel is just covering some results in Sheet 2.
I need the result to be shown up until a certain row, skip a few rows and then resume.
.
Also, I just realized IMAGE 3 is wrong. From row 11 forward, the results displayed should be:
11…11/01/2025
12…12/01/2025
13…13/01/2025,
etc.

[joshua4]

Possible, yes. Practical???

I don’t know what your actual usage case is here, but certainly “flowing” data around existing data is contrary to the logic of any spreadsheet program.

I see several options if this really needs to be done, but they depend on the actual usage cases. Here are some examples:

1. If there is actually a column of unique index numbers that are a sequence, then simply hand re-create that sequence around the “red” data and use VLOOKUP or the INDEX(…MATCH(…)…) combo to lookup the rest of the data based on these.
   
   
2. Instead of hand-creating the index sequence, interfile the sequence from Sheet1 onto another sheet, Sheet3, based on the “red” data on Sheet2, then use the VLOOKUP or INDEX/MATCH. The “red” data will no longer be red, but it will be interfiled into the data. Here is an example of a BASIC macro that would do that:

Function Interfile(A As Variant, B As Variant)
	Rem Uses dimension defaults based on data in a column
	Rem B has the "red" data
	Dim C() As Variant
	Dim IC As Long
	Dim IA As Long
	Dim IB As Long

	ReDim C(1 To (UBound(A) - LBound(A)) + (UBound(B) - LBound(B)) + 2, 1 To 1)
	IA = 1
	IB = 1
	For IC = 1 To UBound(C)
		If IB > UBound(B) Then
			IF IA <=UBOUND(A) Then
				C(IC,1) = A(IA,1)
				IA = IA + 1
			EndIf
		ElseIf IsEmpty(B(IB,1)) Then
			IF IA <=UBOUND(A) Then
				C(IC,1) = A(IA,1)
				IA = IA + 1
			EndIf
			IB = IB + 1
		Else
			C(IC,1) = B(IB,1)
			IB = IB + 1
		EndIf
	Next IC
	Interfile = C
End Function

Which would be entered as an array formula on Sheet3, then the same sort of VLOOKUP would be used as under 1. above, but on Sheet3.
3. The macro under 2. above could be expanded to automatically transfer multiple columns of data.

See attached ODS for the single-column example.
Interfile VSTACK.ods (13.9 KB)