Excel query

  Dirty Dick 12:11 05 Oct 2007

I have a workbook that I use to keeps records of rental items. I have one page as an annual calendar, with a column that has the rental price. As people pay their rental fee, I change the colour from red to black. I would like to have two cells, one that has the actual fees already paid, and one that forecasts the ammount due for the year. Is there any way I can format these cells so that this all works. I think the annual forecast cell would just be a plain sum. Any ideas?



  VoG II 13:14 05 Oct 2007

Conditional Formatting should do what you want. See click here and click here

  Dirty Dick 13:51 05 Oct 2007

I've probably not explained myself very well, and I apologise for that.

My worksheet has a column that has the rental ammounts entered, even when forecasting say for 2008. The fee for some has not yet been paid so I leave the entry in red, to show as a debt owing. Some people have paid so I change their entry to black. I have summed the column up, but that just gives a total that will have been paid WHEN everyone has given their fee. I call this a forecast total. Thats the easy bit! each fee is the same per week, and only in weekly amounts. Is there any way I can set up the workbook so that when someone pays their fee, the cell will be set to change from red text to balck text (or some other formatting)



  silverous 15:14 05 Oct 2007

Even after your 2nd explanation it still sounds like conditional formatting is the answer!

  skeletal 16:00 05 Oct 2007

I think I would have a second column, next to the one with the amounts of money in. In this second column I would type “P” when they paid.

E.g. if the amount is in cell A2, you would type “P” in B2 when that person paid.
Using conditional formatting in A2, I would have the format to say “Formula is….=B2="P"….and chose format font to the colour green.

Then ADD a new condition: =B2<>"P" and chose format font to the colour red.

I would add a third column and in that column I would have =IF(B2="P",A2,0). This would copy over the amount if P was present, and put 0 if not. Adding this column sums the amounts actually paid.

So you end up with red and green text, and a new column of amounts paid.

Is this what you mean??


  Dirty Dick 20:52 07 Oct 2007

Thats excactly what I mean......... HOWEVER, when I use your details I can't get the 3rd column to bring the details from A2 across to C2 when the letter 'p' is put into the middle column. I have set the fill to green, and this works, but the figure in A2 is not brought across, and neither displays a '0'.

Any help would be appreciated


  skeletal 21:59 07 Oct 2007

I am very puzzled DD. I just tried my own instructions and it worked. You have to be sure that the syntax is exactly as I wrote. Indeed, if you cut and paste the =IF(B2="P",A2,0) directly from my post, into C2 (with the number in A2 and the p in B2), it should work.

If you still can’t get it to work, PM me with the dodgy sheet (just the trial, not your main one) and I’ll try to see what is wrong.


  Dirty Dick 22:18 07 Oct 2007

How do I attach the file tp a pm?

  skeletal 22:20 07 Oct 2007

Oops! If you PM me then we can exchange emails as we will then know each other's address.


  Dirty Dick 09:47 08 Oct 2007

After passing e-mails between myself & Skeletal, the problem has been resolved.

In the 3rd column I had been trying to use conditional formatting and pasting the formula =IF(B2="P",A2,0)which only gave me green fill formating, and did not bring over the figures from the 1st column. I have now put that formula into the cell itself, and the figures are copied across.

Thanks to all who responded

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

The art of 'British' pulp fiction

Best password managers for Mac

TV & streaming : comment regarder le Tournoi des Six Nations 2018 ?