# Auto increment cell reference in a formula

Hi,

I have the current formula in Calc:
=(((IF(\$‘Balaton 2018RAW’.H7596>30;30;\$‘Balaton 2018RAW’.H7596))+(IF(\$‘Balaton 2018RAW’.J7596<10;10;\$‘Balaton 2018RAW’.J7596)))/2)-10

The reference to the cells should increase every time by 48 when I pull down the selection.
So, the next cell should contain the value:
=(((IF(\$‘Balaton 2018RAW’.H7644>30;30;\$‘Balaton 2018RAW’.H7644))+(IF(\$‘Balaton 2018RAW’.J7644<10;10;\$‘Balaton 2018RAW’.J7644)))/2)-10

For now I’m changing the reference manually, but I would like to get this automated.

Is there a way to do it?

Thanks a lot,
Gregory

Use INDIRECT. For example, enter `7596` in A1, `=A1+48` in A2, and drag down from A2. Then in B1, enter the following formula and fill down.

``````=(((IF(INDIRECT("'Balaton 2018RAW'.H" & A1)>30;30;INDIRECT("'Balaton 2018RAW'.H" & A1)))+(IF(INDIRECT("'Balaton 2018RAW'.J" & A1)<10;10;INDIRECT("'Balaton 2018RAW'.J" & A1))))/2)-10
``````

The formula could be shortened by adding more helper columns to include the entire cell address.

``````="'Balaton 2018RAW'.H" & 7596 + (ROW() - 1) * 48
``````

To avoid using any helper columns, replace `A1` with the calcuation using ROW.