paste exact same formula to every cell in a column in google sheets

Question

I am trying to apply a formula to a whole column but when i type the formula in the first cell of the column and dragging down the cell from the right corner, the sheets changing the formula.

Can you guys help me?

Here is my formula;

=IF(C:C=Maliyetler!A1;Maliyetler!B1;
IF(C:C=Maliyetler!A2;Maliyetler!B2;
    IF(C:C=Maliyetler!A3;Maliyetler!B3;
        IF(C:C=Maliyetler!A4;Maliyetler!B4;
            IF(C:C=Maliyetler!A5;Maliyetler!B5;
                IF(C:C=Maliyetler!A6;Maliyetler!B6;
                    IF(C:C=Maliyetler!A7;Maliyetler!B7;
                        IF(C:C=Maliyetler!A8;Maliyetler!B8;
                            IF(C:C=Maliyetler!A9;Maliyetler!B9;
                                IF(C:C=Maliyetler!A10;Maliyetler!B10;
                                    IF(C:C=Maliyetler!A11;Maliyetler!B11;
                                        IF(C:C=Maliyetler!A12;Maliyetler!B12;
                                            IF(C:C=Maliyetler!A13;Maliyetler!B13;1)
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

Welcome to SO! The issue is that you should fix it. When you drag it, A1 will become A2 and so on. You should place, instead of A1, $A$1, so you will point always the same item. — David García Bodego
This looks like an x-y problem. meta.stackexchange.com/questions/66377/what-is-the-xy-problem Probably you should be using a lookup rather than such a big nested if statement, but we don't know what problem you were trying to solve in the first place. You may wish to edit your question so that we can help you better. — Tom Sharpe

Unknown Unknown · Accepted Answer · 2020-01-10T10:46:45

As David pointed out in the comments, a dollar sign $ is used to fix values in formulas in Excel and Google Sheets. It is basically an indicator to the fill handle not to change this value when copying this formula.

Example:

=A1+A2

Dragged down will become:

=A2+A3
=A3+A4
etc.

But

=A$1+A2

Dragged down will become:

=A$1+A3
=A$1+A4
etc.

The dollar sign can be used to fix any value, but only works for the column or row once. So if you fill it sideways and don't fix the column:

=A$1+$A2

Will become:

=B$1+$A2
=C$1+$A2
etc...

paste exact same formula to every cell in a column in google sheets

1 Answers