02-23-2019 07:51 AM
02-23-2019 07:51 AM
Hello All, I'm hoping this is an easy question for the Excel vet's but I'm going round in circles and would appreciate your help.
I have a simple spreadsheet being used to record credits and debits for my wife's small home business. She only used to accept cash but can now accept card payments. For each card payment the card company charges her a flat rate of 1.75%. So, for example, for a payment of £100, it costs her £1.75 - simple so far!
As her charges are different for the services she provides I am entering the credits manually but I would like to be able to complete the debit entry automatically based on the information in the cells above that show the credit. Please see the example below:
DATE DETAIL TYPE DEBIT CREDIT
23 Feb 19 Job 1 Cash £150
23 Feb 19 Job 2 Card £100
23 Feb 19 Card Fees Electronic £1.75
As can be seen, the last line shows that £1.75 has been entered as a debit from the calculation 1.75% of £100 (taken as a credit/payment in the line above).
I would like to be able to automatically complete ALL of the cells in the bottom line simply by the spreadsheet recognising that the Card entry has been made in the line immediately above it. I will also need to then be able to continue manually adding credit rows down the spreadsheet over time, and the next time a card payment is made, the system then automatically completes the debit row again.
I hope this is as simple as I think it should be but thank you in advance to anyone who chooses to respond.
Regards - Mark.
02-24-2019 02:59 AM
I presume, you copied & pasted your excel table from your MAC into your post? Unfortunetaly I made the same as I tried to figure out a solution for you. My Excel broke because of the mac (taiwanese) formattings in there... But i finally have a solution for you. see my screenshot.
Formula name in german:
SVerweis(the cell you are searching within a matrix, then the matrix, then the number of columns in the matrix, then a zero for find the exact first number in your matrix)
NOTE: In german formulas we have the ";", you have to write it with a comma!
The formula name is in english: VLOOKUP and you need a second list (matrix is a table without the headers!) in the right side next to your calculating list column.
Additionally i converted the first list as a table object. It behaves like a common database and is perfect for doing things automatically in the next lines.
I wish you a nice weekend. Greets, Eva.
02-24-2019 07:43 AM
Hi Eva, Thank you for taking the time to look at this problem.
I'm not sure why Taiwanese formatting is there as I have never used this language. Also, I did not copy and paste the table, I manually typed it into the message screen to look similar to my Excel page.
It's been a long time since I have had to use VLookup but I think I understand what you are saying.
Thank you again for your advice.
02-24-2019 07:50 AM
sorry. There was an issue on my own excel app to cause that cell formatting trouble within my sheets..., was my fault. Say, if it works so far with the vlookup. It could be easier to remind some good tricks like the tutorials about it on youtubes channel called "ExcelIsFun", check this out!
02-26-2019 10:00 PMSolution
02-27-2019 07:40 AM
That's sorted me out - fantastic thank you.
It was my incorrect use of the "double quotes" that was messing things up.