Excel - perhaps conditional formatting ....

  Dirty Dick 13:40 27 Feb 2010
Locked

I’m trying to help a friend set up an excel workbook to assist him in keeping track of invoices that have been paid or are outstanding. He runs a Care Home and has 30 residents, some are paid for by the Local Authority (every 28 days) and some are paid for privately (monthly).

Unfortunately most fees are different, and change annually; perhaps I could have a cell that we could put the different fees in, and change this as and when necessary. Most payments go into the bank directly.

We are thinking of starting a workbook for every year, and 12 worksheets (one for every month) plus a summary sheet. On each sheet the columns across the top would be the days off the month i.e. 1 to 31, and the rows would be the people’s names.

I want to set up, perhaps conditional formatting ?, so that as a payment is made, the amount would automatically be put into a cell that has a total for that person, and an figure taken over to the summary sheet.

Also if a payment is overdue, the cell would highlight to a red fill. Again, perhaps conditional formatting ?

Someone has suggested using a Macro, but I have never used macros and have no idea what to do.

Can anyone suggest a way forward with a way of setting up a BASIC format, which can be tweaked up as time goes by.

Sorry if I'm prattaling on, but I'm not sure how to get going.

Many thanx

DD

  VoG II 13:57 27 Feb 2010

So, let's say that names are in column A, presumably a payment would be entered in one of columns B:AF, depending on the day of the month. Or do you mean something like enter an X in a cell when Fred's payment is made and then have Fred's individual charge appear in, say, column AG?

  Dirty Dick 14:02 27 Feb 2010

Hi VoG™ , I think your second suggestion would be the best option

  VoG II 14:17 27 Feb 2010

The more I think about this the more I think that a financial package like Microsoft Money or Sage Accounting would be better than Excel for this. Nevertheless...

Create another sheet, I called mine Payments, with a list of people in column A and their fees in column B. Now on your month sheet in AG2 enter the formula

=IF(COUNTA(B2:AF2)=0,"",VLOOKUP(A2,Payments!A1:B1000,2,FALSE)*COUNTA(B2:AF2))

and copy down as far as needed.

Now, if you enter X (or indeed anything) in columns B to AF the payment, multiplied by the number of payments that month* will appear in column AG.

* I did this because I assume that the 28 day payers could have more than one payment in a calendar month.

Assuming that your monthly sheets are consecutive when you look at the sheet tabs, reading left to right, then to get the total payments for all months in a summary sheets you can use a formula like

=SUM(Jan:Dec!AG2)

  Dirty Dick 11:35 13 Mar 2010

thanx VoG™

the more I look at it, the more I think a financial package is more suitable.

I have been tweaking LOTS & LOTS of things, but it gets bigger & bigger, so will call it a day.

many thanx again.

DD

  skeletal 19:48 13 Mar 2010

In the last few minutes before calling it a day, have you thought about Microsoft Access?

I always like to get a quick idea of overall size of data before starting a project like this. Assume 30 residents, assume 3 years residency per person, assume 12 payments per year, assume the business uses this software for 40 years. A quick calc gets the order of 200,000 sets of data (everything is in one place so you could, many years after setting it up, run a query to find out payment details 40 years earlier).

This is small change to Access. You could also put in all the drug details for every resident as well if you were so minded.

The downside is that to set this up would be much less intuitive than Excel. However, you may wish to consider asking a database developer to tailor make an application for you. It may be too expensive though.

Perhaps someone has already come up with a “care home” computer system?

Just another thought.

Skeletal

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

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

This is what design agencies will look like in 2032

How to update iOS on iPhone or iPad

WhatsApp : comment lire vos messages sans que l’expéditeur le sache