Excel Help

[Eklipze3k]

OK, say I have two sets of data on 2 different sheets within the same workbook.

Sheet1 contains Customer Reference and Amount Owed
Sheet2 contains Customer Reference and Amount Paid

Not everyone is going to pay, and those that do do not always pay the full amount.

What I currently have is a VLOOKUP to see if the customer has made a payment (regardless of how much). What I need though, is some way of showing whether or not that person has paid the full balance and, if they haven't, what the correct current balance is (doesn't necessarily need to be a single formula but if you can do it in one I'll be impressed).
Have had a look through excel help but not really sure how to phrase the search Anyone any ideas?

 

[JamesL]

can you not use an argument? use =IF((N31=N41),0,(N31-N41))

Where N31 is the amount owed and N41 is the amount paid.

Thats uses 'argument, value if true, value if false'. which means the argument is whether or not the amount owed is the amount paid. If true, the value is 0, if false (ie the amount owed is not the amount paid) then value is amount owed less amount paid.

Set up a new page showing a debtors list, list all cutomers and then insert that formula into the amount outstanding column. You can link it to other sheets if you use '[insert name here]'!N41 where N41 is again the amount paid. Make sure you include the ' ' as well, or it wont work. HTH, and it makes sense!

 

[Eklipze3k]

can you not use an argument? use =IF((N31=N41),0,(N31-N41))

Where N31 is the amount owed and N41 is the amount paid.

Thats uses 'argument, value if true, value if false'. which means the argument is whether or not the amount owed is the amount paid. If true, the value is 0, if false (ie the amount owed is not the amount paid) then value is amount owed less amount paid.

Set up a new page showing a debtors list, list all cutomers and then insert that formula into the amount outstanding column. You can link it to other sheets if you use '[insert name here]'!N41 where N41 is again the amount paid. Make sure you include the ' ' as well, or it wont work. HTH, and it makes sense!

Would work in theory (and statements like that are used in the sheet) but when your talking about 16,000 customers and daily payments which may or may not include any of those customers, I was hoping for something more automated than having to do IF statements for each individual cell.

Cheers though

 

[Focus Owner]

why arnt you using a true databasing program?
i have no ideas tho

 

[Eklipze3k]

why arnt you using a true databasing program?

Necessity rather than choice

 

[adamcot]

Id use the SUMIF function, It will add up if a certain criteria is met.

The formula is =sumif(range,criteria,sum range)

I use this a lot at work, along with SUMPRODUCT

 

[Eklipze3k]

Id use the SUMIF function, It will add up if a certain criteria is met.

The formula is =sumif(range,criteria,sum range)

I use this a lot at work, along with SUMPRODUCT

Again a good idea, but not quite what I need. I need the individual accounts from the original list (which is static) flagged in some way if they have made payment(s) (the list of payment is updated daily and doesn't always include accounts from the original list), and the revised balance to be shown ... if that makes sense.

 

[JamesL]

it probably makes sense but not to me! It is automated, its hardly like you have to type out each cell by itself, providing you have all the customers in the same order on every sheet, you can just drag the formula down and it will do it itself.

Do you mean you want a list that only shows the values if they still owe money? And doesnt include any nil balances?

The really simple answer is get Sage line 50 - a proper bookeeping package. As well as making it easier for you your accountant will have an easier job too.

 

[Eklipze3k]

it probably makes sense but not to me! It is automated, its hardly like you have to type out each cell by itself, providing you have all the customers in the same order on every sheet, you can just drag the formula down and it will do it itself.

Do you mean you want a list that only shows the values if they still owe money? And doesnt include any nil balances?

The really simple answer is get Sage line 50 - a proper bookeeping package. As well as making it easier for you your accountant will have an easier job too.

Unfortunately Tesco are cheap and will not pay for a decent package for us to use, no matter how much easier it will make life.
But the bit I have put in bold is the problem. The data on Sheet1 is in order based on how much each individual customer owes (16k accounts, from £10 to £3,000+), and stays that way. Sheet2 - where the payments are added daily - are copied and pasted from another source, in the order in which payments were taken. As advised, not every payment will relate to an account on Sheet1, and those that do will not always be the full balance.
I think whatever I use will involve a fair portion of manual faff given that I'm stuck using excel

 

[adamcot]

Well you could then use another IF statement to check that one column equals another, so if the summed column reaches the expected balance, it says "0", and a minus for each for outstanding balance.

With regards to the sheets changing daily, could you not just leave the formulas set on one sheet, and change the other sheet so that the cell refs dont move?? They then dont have to be in the same order all the time?

If you want, post an example sheet on here and il have a look and see if I can work it out

 

[JamesL]

sometimes excel can be funny about changing where a formula relates to - if its directed at one cell in particular the formula will track wherever you change the original cell to. That if statement sounds the same as the one I mentioned above?

Presumably the 16k odd customers have specific customer references used. you could use the 'sort' function to organise the customer lists into the same order on every page. if you did this every time new information was added then it would keep every page in the same order. Providing excel directed the formula to a cell, rather than the information inside the cell, the above 'if' argument could be used.

However this would mean that when a customer made multiple payments on the same order, you would have to manually reconcile the payments, as you would have one line saying they owed £30, then the line below (which shows the balancing payment) showing an over payment of £30, as it has no invoice to match against. Its fine for easy ones like that but any complex payments on accounts and you are basically back to square 1.

Seriously, tell them the main gain here is not measured by the cost of the new software, but the efficiency savings of your time - imagine the extra work you could do if you didnt waste so much time on here, I mean dealing with that.

There are other packages you could get relatively inexpensively to do this. Tesco are being ignorant if they think you can accurately and quickly track 16,000 customers with excel to the level you need.

There must be a formula you could use where it 'groups' all information on the same customer reference. Perhaps pivot tables are the way forward, if you used criteria like the 'if' statement above combined with criteria in the pivot table of any lines with figures in positive. again maybe a bit more research needed.

Pivot tables could probably do it somehow but it would be even more complicated.