Excel formula question

cubix

I am looking to setup a formula in Excel so when I put the same figure in "Amount Paid" that is in " Total Due" it will automacticly put the word "Fully paid" in both the "Total Due" and "Balance owing" boxes, is this possible?

"Total Due" is in colum (D2)
"Amount Paid" is in Colum (i2)
"Balance Due"is in Colum (J2)

Thanks

Genghis

Try this formula to put either the amount due or the word "Fully Paid" into Balance Due Column: =IF(I2=D2,"Fully Paid",D2-I2).

samhail

looks up conditional formatting too to change the color of it

cubix

Cheers for that GengHis but getting this error. Either way not sure if it will be a runner as I need to repeat this step all the way down Line 1/2/3/4/5 etc so not sure if it is more work than its worth?

Thoie

I've started this about 3 times

You're going into circular references there that just don't work. Also, in your graphic, you seem to be putting the formula in the wrong column to start with.

So.
"Total Due" is in column D
"Amount Paid" is in Column I
"Balance Due"is in Column J

Let's say for one particular thing, Total due is €10 - so the value of that cell is 10.

Amount paid is 10.

In Balance Due you want a formula that will say "Fully Paid". So in J2 type

=IF(I2=D2,"Fully Paid",D2-I2)

Now, highlight cell J2, and hover your mouse over the bottom right hand corner, click on the black square and pull straight down column J. This copies your formula all the way down (you don't have to type it for each row!). See the bottom of this page for a picture of the "Fill handle"

Now step back and think. This works at the moment because the contents of D2 is 10. If you do something to change the contents of D2 to Fully Paid, then D2 will no longer equal I2, and therefore you start going around in circles.

So think about why you need The Total Due to say fully paid? If the words Fully paid appear once on a row, why do you want it to appear twice? What is your end goal? What are you trying to achieve by having the words Fully Paid appear in column D as well as column J?

cubix

Thanks for pointing that out Thoie. Your spot on as regards going around in circle. The reason I was hoping to be able to have "Total Due"(d) change to fully paid is because I usually forward on only the first three rows to show whats out standing. The other rows are really just for me so I can auto sum for vat purposes at the end of the month if that makes sense?

Thoie

cubix wrote: »

Thanks for pointing that out Thoie. Your spot on as regards going around in circle. The reason I was hoping to be able to have "Total Due"(d) change to fully paid is because I usually forward on only the first three rows to show whats out standing. The other rows are really just for me so I can auto sum for vat purposes at the end of the month if that makes sense?

Aha, that does make more sense.

You have a couple of options, depending on what you do at the moment.
If you currently just copy the first few columns into a new spreadsheet to send on to a boss or someone, then the solution is to highlight just the columns you want (one at a time if you're not comfortable with multiple selections - To highlight nonadjacent columns, hold down CTRL while you select nonadjacent columns). Copy the columns you want to send on, then right-click in the new spreadsheet (cell A1), "Paste Special" and pick Values. In your case you probably want columns, A, B, C and J.

Alternatively, if it doesn't matter if the people you're sending it to have access to the VAT and other details, you can just hide the irrelevant columns before sending it to them - the columns are still there if anyone wanted to see them, but they won't be visible unless deliberately unhidden.

Take a look at some of the training info for Excel - there's a lot of features in it that you might find useful. If you can put up with the accent, I recommend Get to know Excel 2007: Enter formulas.

cubix

Much appreciated;)

cubix

Just a quick one Genghis/Thoie is it possible to add into the sum/formula so when "Fully Paid" comes up its in Green

Thanks

Thoie

cubix wrote: »

Just a quick one Genghis/Thoie is it possible to add into the sum/formula so when "Fully Paid" comes up its in Green

Thanks

It's not specifically into the formula, it's the conditional formatting samhail mentioned earlier.

Basically you'll set it so that if the cell = "Fully paid", colours will change.

cubix

Thanking you

CCSL

Kind of combining the options above

Add another column next to Balance due and put in a formula like this
=IF(balance due>0,total due-amount paid,"Fully Paid") Then hide the balance due column and put a label on the new one of Balance due (replace names with your cells)

You could move this column to near the total due column also for easy copy and paste.

for the conditional formatting you could use cell value <> "fully paid" to highlight the uncleared amounts.

you can protect your hidden columns from the review tab protect sheet put a password on it and then no one without it can unhide your columns.