Drag down a formula

[Perlapimpim]

Hi

I have 2 tabs. In the tab TEST I want to reference a cell from the tab "CALCULATION". Below this cell, I need to reference the same cell as above.
But when I drag it down, it will jump a few references:

I have my 4 first cells done manually
A2
A3
A4
A5
when I drag down I get
A14
A15
A16
A17
A26
A27
...

Could someone explain to me what I have to do in order to reference all the cells from the calculations tab in the right order, please?

[Sam Capricci]

Can't tell from your snapshot but here are some basics to go by.
=A2 when dragged down will become =A3, and when dragged right will become =B2
=$A2 when dragged down will become =$A3 and when dragged right will become =$A2 the $ before the column locks in the column.
=A$2 when dragged down will become =A$2 but when dragged right will become =B$2 the $ before the row locks in the row.
=$A$2 when dragged down or dragged right will stay locked on A2 as the $ before the column AND the row locks in the cell.
hope that helps.

[Perlapimpim]

Thank you for you answer, but unfortunately it does not help

[63falcondude]

I recommend uploading a small representative sample workbook (not picture) of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[Perlapimpim]

Here is a sheet with a sample
so I did the 3 first ones manually but I highlighted the cells who are wrong
There are thousands of references so I need to find a solution
One of the lead was to reference the cell number, but I haven't found a way yet

[Sam Capricci]

try this in C3 of the ref tab... =IF(A3="",INDEX(Calculations!$A$2:$A$47,ROUNDUP(ROW(A1)/3,0)),"")

EDIT, actually in B3 then dragged down, got confused with where I put it instead of your results.

[Perlapimpim]

Thank you so very much Sambo Kid!!! it's very helpful

[Sam Capricci]

You're welcome! Don't forget to mark the post as solved if that takes care of your issues using the thread tools dropdown at the top of this post.

[kvsrinivasamurthy]

Try this.
ARRAY formula in 2nd row then copy down.

=IFERROR(IF($A2<>"","",IF(SUM(OFFSET($A2,0,0,-3))=0,"",INDEX(Calculations!$A$2:$A$47,MAX($A$2:$A2)-ROW($A$2)+1))),"")

How ARRAY formula is entered

Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.