SOLVED

Auto filling fields and calculations

Copper Contributor

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.

 

 

 

6 Replies

Hi Emo66!

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.

 

 

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.

 

Regards,

Mark.

Hi Mark,

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!

 

Greets, Eva.

best response confirmed by Emo66 (Copper Contributor)
Solution
Hello Mark,
Assuming your Table Labels are in A1:E1, your formulas you need are:
A2=IF(C1="Card",A1,"")
B2=IF(C1="Card","Card Fees","")
C2=IF(C1="Card","Electronic","")
D2=IF(C1="Card",E1*0.0175,"")

Hi Twifo,

 

That's sorted me out - fantastic thank you.

It was my incorrect use of the "double quotes" that was messing things up.

 

Thanks again.

Mark.

Welcome, Mark.
1 best response

Accepted Solutions
best response confirmed by Emo66 (Copper Contributor)
Solution
Hello Mark,
Assuming your Table Labels are in A1:E1, your formulas you need are:
A2=IF(C1="Card",A1,"")
B2=IF(C1="Card","Card Fees","")
C2=IF(C1="Card","Electronic","")
D2=IF(C1="Card",E1*0.0175,"")

View solution in original post