[mark dew]

I wonder if anyone can help with this. My monthly account template looks roughly like below. When i have been paid for a job i put a Y in the paid column or an N if not. What i would like to do is in the paid column, if there is an N i would like to be able to highlight the background red. And green if there is a Y. Does anyone know the formula which will allow me to do this?

DATE     ADDRESS   COMMENTS   COST   PAID   MILEAGE   EXPENDITURE   
12/10    twilight...                      £15       Y                              £10
12/10    hall farm                       £25       N           28               
 
WEEK ENDING 7 DECEMBER         £40                    28                £10
DATE     ADDRESS   COMMENTS   COST   PAID   MILEAGE   EXPENDITURE   
9/12      torah                            £10       Y            15              £100

etc etc.

[Bonkors]

I think you are talking about "IF" functions but i dont know if it is possible to tell the formula to colour the cell IF=Y or IF=NO. Best thing to do is go onto the help option and search for "scenario" or "IF" or "Statement" there are many options that come up, see which one is relevant.

Having faild any of the above, just go the simplest way and fill the cell with a colour rather than using a formula

[Paul Coleman]

I wonder if anyone can help with this. My monthly account template looks roughly like below. When i have been paid for a job i put a Y in the paid column or an N if not. What i would like to do is in the paid column, if there is an N i would like to be able to highlight the background red. And green if there is a Y. Does anyone know the formula which will allow me to do this?

DATE     ADDRESS   COMMENTS   COST   PAID   MILEAGE   EXPENDITURE   
12/10    twilight...                      £15       Y                              £10
12/10    hall farm                       £25       N           28               
 
WEEK ENDING 7 DECEMBER         £40                    28                £10
DATE     ADDRESS   COMMENTS   COST   PAID   MILEAGE   EXPENDITURE   
9/12      torah                            £10       Y            15              £100

etc etc.

I don't use Excel myself and the spreadsheet I use from MS Works doesn't have this facility.
However, if it can be done you would find the solution in the microsoft.public.excel   series of newsgroups.
I did a quick search for you (using the search words  color   cell   if   in the excel newsgroup area)  and you would probably be able to get your solution if you go to:

http://groups.google.co.uk/groups/search?hl=en&q=color+cell+if&qt_s=Search

and try one of the links on that page.

Remember that usenet can be a bit like the wild west so check for an FAQ first to see if your answer is there.  If not and you need to post a query, don't forget to state your operating system and the version of excel you are using.
You have tried the help files in the software I assume?

[Elginn]

Try Round Tracker its the cheapest software out there (£10) and uses what your after in excel
http://www.windowcleaningresources.co.uk/html/round_tracker_.html

[Village Gleam]

The above if answer is probably the right course.

Shiner the excel programme not only has lots of functionality, but lots of help. It links you straight to a place called microsoft office that has tutotials and will answer any question.

[mark dew]

Thanks for your answers. I've done it now.
The background of the Y is highlighted green and the background of N is highlighted red.
If anyone is interested:
> highlight the column
> format > conditional formatting
> condition 1 'cell value' is 'equal to' '="Y"
> click on format
> click on patterns
> then click on the background colour you want ie green for me
> then click on ok

then click "add" button at the bottom of box and repeat the above but change the Y for N and choose the background colour as red instead of green.

Thanks for your help

[Paul Coleman]

The above if answer is probably the right course.

Shiner the excel programme not only has lots of functionality, but lots of help. It links you straight to a place called microsoft office that has tutotials and will answer any question.

Is that an ONLINE help facility that MS update periodically.  If so, it sounds much better than the old style help files that come with a program.
Come to think of it, I may have used similar myself with my MS Publisher 2003 program. Maybe I've got so used to doing things the old fashioned way, I've just got into the habit of it.
There's no doubt that Excel is a far superior program to the Works 6 that I use.  To upgrade though would mean me paying a fair bit just for a few extra features that I get by without anyway.
I may have another stab at the "Openoffice" program sometime (said to be almost completely compatible with MS Office and free of charge too)

[DASERVICES]

Mark you will probably end up doing your spreadsheet several times before you get it right.

Couple of tips, income and expenditure have them on seperate worksheets. Then have your accounts linking to both.

Income keep it basic :-

Start Date, Ceased Date, Address, Comment, Price.   Date Worked Price, Date Worked Price, Total

You could also add last date worked, weeks etc...

Expenditure you do not need to keep a daily tab of mileage, just record start and finish each month.

Keep it simple so you are not inputing loads of data , then run reports behind all the figures which you require to see how well your business is running.

[Village Gleam]

Yes you have used similar shiner with your publisher 2003. I can tell you know lots more about computers than i do- but I just jumped straight in and bought a top spec comp with bt broadband and the full small business office 2003. I also have publisher frontpage and one note george and sage.

But you can do things with your microsoft works spread sheet that I can't. My view is that life's too short.

Mark Dew the answer you found is helpfull because as you infer anything outstanding will be easier to spot.

I struggle with accounting for cash.

There are three basic financial statements cash flow,profit and loss, and the balance sheet. DA is suggesting a sort of hybrid.
Somewhere in that is a bank reconcillitation, but it is quite fiddly, for us because we have so many small cheques, bill payment tranfers, small cash payments, payments for extra's and late payments that it is not always practical to alter george for all eventualitys. The George guy says that later versions will include the ability to tick off cash or other which would help.

If anyone has already cracked this please say.

[Paul Coleman]

Yes you have used similar shiner with your publisher 2003 shiner. I can tell you know lots more about computers than i do- but I just jumped straight in and bought a top spec comp with bt broadband and the full small business office 2003. I also have publisher frontpage and one note george and sage.

But you can do things with your microsoft works spread sheet that I can't. My view is that life's too short.

Mark Dew the answer you found is helpfull because as you infer anything outstanding will be easier to spot.

I struggle with accounting for cash.

There are three basic financial statements cash flow,profit and loss, and the balance sheet. DA is suggesting a sort of hybrid.
Somewhere in that is a bank reconcillitation, but it is quite fiddly, for us because we have so many small cheques, bill payment tranfers, small cash payments, payments for extra's and late payments that it is not always practical to alter george for all eventualitys. The George guy says that later versions will include the ability to tick off cash or other which would help.

I anyone has already cracked this please say.

MS Works spreadsheet isn't as flexible as Excel but it can be adapted adequately for a one man window cleaning business and beyond.  However, to achieve this, I find I need to use it with the Works database as well.  It took a bit of trial and error setting it up, but it's very easy to maintain.  That's why I chose to do it that way.  All I do is use a field in the database to record individual customer debts.  When the debt is paid, I overwrite it as £0.00  .  I have a report set up to show any records that are "<>£0.00" which gives a printable  list of anyone in debt or in credit.  Like you say, life's too short.  That's why I put a lot of effort into setting it up so it would be a doddle to maintain afterwards

[mark dew]

Mark you will probably end up doing your spreadsheet several times before you get it right.

Couple of tips, income and expenditure have them on seperate worksheets. Then have your accounts linking to both.
Income keep it basic :-
Start Date, Ceased Date, Address, Comment, Price.   Date Worked Price, Date Worked Price, Total
You could also add last date worked, weeks etc...
Expenditure you do not need to keep a daily tab of mileage, just record start and finish each month.
Keep it simple so you are not inputing loads of data , then run reports behind all the figures which you require to see how well your business is running.

hiya DA, I have, date worked, address, comments (conservatory roof or extra etc), paid (yes or no), mileage, expenditure and expense type columns. These are totalled weekly and also totalled up at the end of each month and manually transferred onto separate income and expenditure sheets. The expenditure sheet just breaks down each expense type and is totalled monthly and yearly.

I have to take the totals and manually input them onto the yearly income sheet and yearly expenditure sheet. I don't know how to automatically link the info from the monthly sheet to the yearly sheet.

the totals of my monthly sheet looks for example like this:
JOBS FOR DECEMBER               120   
                  
MILEAGE FOR DECEMBER               268   
                  
EARNINGS FOR DECEMBER               £1860.00   
                  
EXPENDITURE FOR DECEMBER               £316.24   
                  
AVERAGE EARNINGS                                            £15.50   

PROFIT FOR DECEMBER               £1543.76   

I have  a separate excel sheet with titles "cheques for december" etc which details date, address, name on cheque, bank, cheque no, amount and date banked.

I use calendar in outlook to schedule my work and hot synch it to a palm which i carry daily.
What i don't know is how to automatically link the monthly totals onto the yearly income sheet and expenditure sheet. If you know the formula for this and could help i would be grateful.
I did use a trial version of george but like the possibilties of excel and have stuck with it probably through stubbornness. If anyone would like a copy of the templates i use then let me know with an email addy. Also if anyone can see how to improve on this i would be grateful as well.
Thanks for your input DA. keep it coming.

[DASERVICES]

What I have is monthly totals in seperate worksheets which will have all the dates in Jan etc..

Then I use "sumif" to look at my Income spreadsheet and returns the total value for that date. For example it will look at all the jobs I have done on the 2nd Jan and return the total value.

I then have a total value calc for Jan "sum" of all cells

On my accounts sheet I have Income etc.. in monthly columns. So for Jan I just press the "=" key and then link it to the Jan total. This then links to that figure and changes when it has been added to.

Hope that helps

[mark dew]

yeah i think so. I will go away and play.
And thanks for the link shiner. It pointed me in the right direction.