Using a formula to determine whether the next row has data??

[Paul]

Hi Folks,

Hopefully somebody can lend me a hand with this problem.
I have a feeling that I might be trying to step into a basket and lift
myself up!

I have 3000+ rows of data (generated by a formula) on one sheet.
It has, at this stage, grown to a fair size.

I was wondering if it's possible to only put the formula into the
following row, if the preceding one has data.

ie.
A A(formula)
Result Formula
6 sum(d1+e1)
4 sum(d2+e2)
9 sum(d3+e3)
12 sum(d4+e4)
8 sum(d5+e5) ......and so on for 3000 rows.

The problem is that not all of the D and E:E are filled with data
but A:A is filled for 3000 rows with the sum(dx+ex) formula.

At the moment the result does not show if D is empty, but the
underlying formula is still there taking up space.

So in summary, I want the formula above to determine whether to place
another below it, and be self-populating kind of thing.

Paul

 

[Frank Kabel]

Hi Paul
not possible with formulas. You may create a macro to do this but if
the formula does not show anything there should be no problem.

 

[Paul]

Thanks for the response Frank, I'll rephrase and repost in the VB group.

Paul

 

[David McRitchie]

You started here, same peple in the other groups.

You want to generate a formula that looks something like:
E6: =OFFSET(E6,-1,0)+D6

which I'd probably use the fill-handle to copy down. You might
also take a look at my page at least for why to use OFFSET
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

You will probably need to use the fill handle to provide the formula
for existing entries (or to correct the formula, if incorrect)
http://www.mvps.org/dmcritchie/excel/fillhandle.htm

Anyway since you say automatic, I guess the fill handle is out.
So the only way I see to do this is with an Event macro, when
you manually create a new row. Assuming you always put
something into Column A the following Change Event macro
will generate your running balance formula in the same row.
Install by right click on sheet tab, view code, plop code in
only one Option Explicit statement and that is at the top.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Cells(Target.Row, 5) = "" Then
Cells(Target.Row, 5).Formula = "=OFFSET(" & _
Cells(Target.Row, 5).Address(0, 0) & ",-1,0) + " & _
Cells(Target.Row, 4).Address(0, 0)
End If
End Sub