Extend a formula to next row only if the next row contains data

[mc2312]

Looking for a way to extend a formula to the next row but only if the next row contains data

e.g. if C1 contains =IF(A1="","",A1+10)

is there a way to automatically extend the formula to C2 if (and only if) data is entered into A2? And then on to C3, C4 etc.

The idea is to set up a template with only the formula in Row 1, and for the formula to copy down only as far as it is needed - to avoid having to copy the formula down to row 20,000!

I know excel will do this automatically if there are 4 similar rows, but in this situation only the top row can contain the formula.

Thanks!

 

[DanteAmor]

A formula can not create another formula or a formula can not create itself, so to speak.
One way to create the formulas is with VBA code.


With the following code, each time you select sheet2, the formulas are created according to the cells used in column A of sheet1

Private Sub Worksheet_Activate()
    With Range("B2:B" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=sheet1!RC[-1]"
    End With
End Sub

Right click the tab of the sheet you want this to work (sheet2), select view code & paste the code into the window that opens up.

 

[mc2312]

Thanks James. It creates a circular reference.. I now realise the question is simply 'how to copy a formula to a variable number of rows' (the variable number being the count of rows on the other sheet). I have found that you can do this using a macro so it's maybe a good reason to learn a bit about VBA. Thanks again for your help..

 

[mc2312]

Thanks, yes I can't change 'othersheet'

 

[mc2312]

Thanks Dante, that makes sense.. I'll try the code..

 

[DanteAmor]

I'm glad to help you. Let me know if you have any doubt.