Moto-Mgmt.

This is where I am at: I created a new workbook ... with two sheets.

The first I named SALES ... the second TO BILL.

In the first one I have my customers f&l, what they purchased and the purchase price ... 4 different columns.

In the second (TO BILL) I used the insert function which automatically enters the customer in the same position from sheet one. Next column I have the amount paid. The column after that I have the balance due ... that I used function to automatically calculate the purchase price from sheet one, less the amount paid from sheet two.

Now ... problem ... can't figure this out with IF or LOOKUP. The next column is INVOICE and I want to automatically have a YES value inserted IF the balance due is greater than 0.

??? How do I do that?

The first row looks like this ...well second row ... I've frozen row one with the column names.

L Name | F Name | Paid | Balance | Invoice |

So my first entry is on line two ... the function I need is in E2. If there is an amount greater than 0 in D2 ... I need it to insert YES into E2??? If not ... than blank or no can be in there but not necessary. Does that make sense?

So ... I click in E2 ... select insert function ... select IF ... then I am lost... or confused from trying everything I can think of and getting nothing but errors. The one time I thought it was working ... I dragged the formula down ... but it put YES in every field.

When I click to insert function ... the window pops up with the following 3 lines that I need to insert something:

Logical_test ___________________________________ = logical

Value_if_true __________________________________ = any

Value_if_false _________________________________ = any

then click okay!

Anyone??? Thanks in advance!

Chevy

Iirc:

=if(d2 > 0,"yes","no")

Moto-Mgmt.

Thank you ... I have tried this ... I get a YES for a 0 balance as well. Just to error shoot which is confusing me even more ... I eliminated the calculated field function ... entered a hard 0 and then I get the result I am looking for.

Moto-Mgmt.

I do appreciate your help. I have tried the other option you mention as well. This has always worked for me in my previous versions.

My actual calculations: Both sheets row one is stationary ... always visible and names my columns. All data entry begins in row two. All $$$ are whole ... no cents.

Sheet1 Named SALES has 4 columns: All fields are manually entered.
L Name | F Name | Item | Cost

Sheet2 Named RECEIVED has 5 columns:

L Name | F Name | Paid | Balance | Invoice

Calculations/functions stated here:
Column1 Row2 auto enter from sheet1 ... used =LOOKUP("Sheet1!",Sales!A2,Sales!A2)
Column2 Row2 auto enter from sheet1 ... used =LOOKUP("Sheet1!",Sales!B2,Sales!B2) Column3 Row2 manual entry amount paid ...

Somewhere from this point on, something is wrong...

This (column4) works ... The difference from the cost of the item and the amount paid ...

Column4 Row2 auto enter ... used =IMSUB(Sales!D2,C2)

This doesn't ... Column 5 ... need a YES to display if there is a balance owed. And this is where I am stuck. If I manually enter a HARD ZERO it works ... not working with balance of zero from a calculated field. ???

cwomack

The "value" of the IMSUB function is actually text, so you can't look it up or reference it like a number.

gistek

Your calculated zero might not be zero. There might be a hidden decimal somewhere in there. This can even happen if you only entered whole dollars in all the input fields and only use add or subtract finctions on them. (Gotta love MS, the only place in the whorld where 2+2=5)

Use the ROUND or ROUND UP function to force the result to be in integer or whole dollar format. It'll look something like this

=ROUND(yourformula,0)
or
=ROUNDUP(yourformula,0)

Next, make sure there are no dollar signs in your formula in Row 2. In your case, this is especially true regarding the row number part of each reference.

=("Sheet1!",Sales!B$2)-C2 will subtract the amount each customer paid from the amount the first customer owed.