Excel query

  Dirty Dick 12:11 05 Oct 07
Locked

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?

Thanx

DD

  VoG II 13:14 05 Oct 07

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

  Dirty Dick 13:51 05 Oct 07

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)

Cheers

DD

  silverous 15:14 05 Oct 07

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

  skeletal 16:00 05 Oct 07

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??

Skeletal

  Dirty Dick 20:52 07 Oct 07

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

DD

  skeletal 21:59 07 Oct 07

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.

Skeletal

  Dirty Dick 22:18 07 Oct 07

How do I attach the file tp a pm?

  skeletal 22:20 07 Oct 07

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

Skeletal

  Dirty Dick 09:47 08 Oct 07

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.

Intel Coffee Lake 8th-gen Core processors release date rumours

1995-2015: How technology has changed the world in 20 years

Framestore’s haunting post-WWII title sequence for new BBC series SS-GB

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…