# Auto increment cell reference in a formula [closed] 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

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-07-20 13:03:47.421101

Sort by » oldest newest most voted 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.

more