SOLVED

Use a Fixed Cell in a Formula which changes based on the row of a column

Occasional Contributor

Dear Experts,

I have a situation where i want to use a fixed cell in column A in a formula in Column B. However, i want to change the reference cell in Column A when i reach row 10+1 in Column B. I need to automate it as it has to be repeated for thousands of lines. I have illustrated it in few pictures.

 

Any ideas would be great.

 

Thanks

 

2 Replies
best response confirmed by AShah16 (Occasional Contributor)
Solution

@AShah16 That could be:

=A1*OFFSET(A1,-MOD(ROW()-1,10),0)

Riny_van_Eekelen_0-1648445809497.png

 

Thanks a lot. It worked great.