Continous cell range in a filtered cells on another sheet or same sheet

Question

I have a data source where I want to assign continuous cell range to the filtered cells only. For example, if I have filtered cell A1,A40,A79,A118,A157 and so on have a gap of 39 cells/rows and I want to assign continuous cell range from another sheet e.g. B1,B2,B3,B4,B5 and so on.

But the moment I put =B1 in the filtered A1 cell, and further drag it down on the rest of the filtered cells, then instead of taking B2 in A40 it's taking as B40. Thus I have to manually assign them by typing the cell range, i.e. in A40 I type =B2, in A79 I type =B3, and so on.

Is it possible to assign a formula to the filtered rows so that it takes the continuous cell range?

Answer 1

Select cells A1 through A352 (or whatever the end of your range is).  Type

=OFFSET(B$1, (ROW()-1)/39, 0)

into the formula box and type Ctrl+Enter (not Ctrl+Shift+Enter).  This will fill each visible cell with that formula.  This computes the relative row number of the B cell that you want to reference.  For example:

 (1-1)/39 =  0/39 = 0
(40-1)/39 = 39/39 = 1
(79-1)/39 = 78/39 = 2

etc.  The OFFSET function then counts down that many rows from cell B$1, so A1 references B1, A40 references B2, A79 references B3, etc.