Auto increment cell reference in a formula

[Greggy]

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

[jimk]

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.