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!
-----
smither fan

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" wrote in message
...
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!
-----
smither fan

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


"Biff" wrote:

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" wrote in message
...
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!
-----
smither fan

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" wrote in message
...
Exactly. Is there no formula that can work around this?
--
smither fan


"Biff" wrote:

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" wrote in message
...
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!
-----
smither fan

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,
--
smither fan


"Biff" wrote:

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" wrote in message
...
Exactly. Is there no formula that can work around this?
--
smither fan


"Biff" wrote:

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" wrote in message
...
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!
-----
smither fan