Custom text in formula/function

[EUROSTOXX]

I have a formula that I am trying to Fill Down, (example =A1 ) but I want the formula to skip every 4 rows, so the fill down should go, = A1, (next cell down)= A5, (next cell down) = A9, = A13 respectively. I tried to use the fill series with steps, but it only worked if I had numbers in the cell, not a formula/reference

any idea

Thanks

 

[Joe4]

Take a look at the INDIRECT function in conjunction with the ROW() function, which returns the current row number.

Let's say in your example:
in cell B1, you wanted to return the value in cell A1
in cell B2, you wanted to return the value in cell A5
in cell B3, you wanted to return the value in cell A9
...

Then in cell B1, enter this formula and copy down as far as you want.
=INDIRECT("A" & ROW()*4-3)

 

[T. Valko]

I have a formula that I am trying to Fill Down, (example =A1 ) but I want the formula to skip every 4 rows, so the fill down should go, = A1, (next cell down)= A5, (next cell down) = A9, = A13 respectively. I tried to use the fill series with steps, but it only worked if I had numbers in the cell, not a formula/reference

any idea

Thanks

Here's another one.

Let's assume you want the results starting in cell D1 and copied downward.

Enter this formula in D1 and copy down as needed:

=INDEX(A:A,ROWS(D$1:D1)*4-4+1)

 

[EUROSTOXX]

Nice! They both work

You guys are amazing! I would have never figured that out.

I will take a look at both functions later on to see exactly how they work but so far they do the job just plug and play.

Thanks again

 

[T. Valko]

Nice! They both work

You guys are amazing! I would have never figured that out.

I will take a look at both functions later on to see exactly how they work but so far they do the job just plug and play.

Thanks again

You're welcome. Thanks for the feedback!