Ask Your Question

Auto increment cell reference in a formula [closed]

asked 2018-06-18 15:45:10 +0200

Greggy gravatar image

updated 2020-07-20 13:03:37 +0200

Alex Kemp gravatar image


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 flag offensive 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

1 Answer

Sort by » oldest newest most voted

answered 2018-06-18 21:26:15 +0200

Jim K gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2018-06-18 15:45:10 +0200

Seen: 807 times

Last updated: Jun 18 '18