Need formula help

[Ross]

Hi everyone,

I'm working in an Excel Database using the Dataform to enter data. I need a
single formula to do several things. Maybe you can tell by the formula I
tried to write, which doesn't work, naturally. It looks like this:
=IF(6:6,SUM(C3+E6)-F6),IF(COUNT(E7:F7)<1,"",G6+E7-F7)

Basically, if this IS G6, add C3 (which is outside the database)+E6-F6, but
if it's NOT G6, then add G6 + E7 - F7. All this needs to go into cell G6.

If I can get it all into that first formula cell (G6), then the formula will
automatically copy down as I enter data into the form ,and it can be sorted
based on the date in row 6 without the formula/calculation going nuts.

Your help is appreciated!

 

[Biff]

You're running into a circular reference:

You want the formula to be in cell G6 and at the same time add some other
cells to the value in G6.

Biff

 

[Ross]

Exactly. Is there no formula that can work around this?

 

[Biff]

So, you want either a value or the formula in G6?

A cell can contain one or the other but not both at the same time. You'd
need some VBA code to do what *I think* you want. I can't help with that.
Maybe someone else will chime in with something.

Biff

 

[Ross]

Hi Biff,

I'm not familiar with VB, so someone would definitely have to take me by the
hand with that! But I would certainly be willing to try.

I actually have a formula setup that works and will copy down, but it will
not sort by date, which is what I need. It consists of:

in cell G6: =Sum(C3+D6)-E6
in cell G7: =If(Count(E7:F7)<1,"",(G6+E7)-F7)

I tried taking the date out of A6, because it's the only date that is not
needed, but then the sort moves the info & formula to the last cell and I get
the "Value" message.

If anyone out there can help me, I would sure appreciate it.

Thanks again,