Eureeka
I have finally got this sussed now. What I did was took on board the previous post and combined it with yours as follows:
I created a new column G and input an =LEFT formula as suggested by Paul Wakefield, and it inserted the first letter of each column so that I could then create a third column H whereby I could then use an If statement to select if the letter I wanted was in, put the monetary total.
It worked great!
Thanks to Paul Wakefield and Paul in Leeds. I have now completed this and will be of a big help going forward. I have clicked Thank and it is showing Unthank on screen, so I am hoping this means I have thanked.
Carolynne
If there are a limited number of possible codes then use if() to put values into appropriate columns.
Hello Johnhemming
I Have tried to A-Z it, but it then puts everything not in date order, just all the rows with Client in order as an example, and I was hoping there would be a way of creating a new table at the side with the original table being kept as it is.
I have tried an If statement, but just don't know how to tell it to look for a word in column E and if that word appears within a row, put the value of that row that is under Column F here (or total all the values in column F that have the word within the text on the rows under column E).
Row one of Column E might say Clients from Birmingham
Row two of Column E might say Clients from Manchester
Row three of Column E might say Prospect 123
Row four of Column E might say Prospect 156
The word is there, but not the only words in the cell
And don't forget in the first year of VAT registration there is a 1% discount off the flat rate you apply to your turnover, until the day before your first anniversary of becoming registered for VAT. So if the flat rate is 12% of £1,200, in the first year of VAT registration it will be 11% at £132.00, populating Box 1, 3 and 5 & as 'cloudcounter' says, £1,200 income going into Box 6 of the VAT return.
HMRC said they were going to automatically apply EORI to VAT registered customers who had not yet registered and if you go to this link you can find out if they have been registered in this way.
In the search box put GB before the clients VAT registration number followed by three zeros 000, press Validation.
It will tell you if they are already set up for it. Normally it also shows the clients name and address on the right, but one of mine asked this the other day (who has not applied) and when I input their number it said they were set up, but it didn't show their company details. Which intimated to me that it was an auto set up by HMRC so I asked the client to phone and check for sure.
Hi Carl
Unfortunately, the client does not use the book keeping package, and would have no idea about posting things online, his knowledge is very limited. This is going to be a problem for customers who are not too savvy with technology.
I do understand what you are saying and that would be great. I just can't get over the point of if a human has already entered it onto a computer (albeit not in the Sage system), that I am supposed to electronically transfer it into Sage from that system and not physically type it again. Otherwise I could simply take the printed copies of the sales invoices from Excel and type them directly into Sage individually. I will have to ponder on all this, as I just can't get over that one point. Thank you for your response Duggimon
Duggimon, re-typing the figures into a spreadsheet from what I have read is what MTD is making us avoid - not re-typing anything and instead using a template or middle ware to import the information typed the first time. I actually don't think you can re-type an entry. So if the client has typed the information into Excel as an invoice in a template in there. My understanding is that I would have to extract those figures using formula (certainly after the soft landing), to bring the figures in.
My answers
Eureeka
I have finally got this sussed now. What I did was took on board the previous post and combined it with yours as follows:
I created a new column G and input an =LEFT formula as suggested by Paul Wakefield, and it inserted the first letter of each column so that I could then create a third column H whereby I could then use an If statement to select if the letter I wanted was in, put the monetary total.
It worked great!
Thanks to Paul Wakefield and Paul in Leeds. I have now completed this and will be of a big help going forward. I have clicked Thank and it is showing Unthank on screen, so I am hoping this means I have thanked.
Carolynne
Hi Accountantccole
I have just tried this formula
=SUMIF(E2:E273,"Client",G2:G273)
(In the sheet I used, the figures are in column G and words are in column E).
It just brings a result of zero '0'
I am not the most proficient with Excel, but I think that with there not just being the one word in the row, it can't see it
Hello Johnhemming
I Have tried to A-Z it, but it then puts everything not in date order, just all the rows with Client in order as an example, and I was hoping there would be a way of creating a new table at the side with the original table being kept as it is.
I have tried an If statement, but just don't know how to tell it to look for a word in column E and if that word appears within a row, put the value of that row that is under Column F here (or total all the values in column F that have the word within the text on the rows under column E).
Row one of Column E might say Clients from Birmingham
Row two of Column E might say Clients from Manchester
Row three of Column E might say Prospect 123
Row four of Column E might say Prospect 156
The word is there, but not the only words in the cell
And don't forget in the first year of VAT registration there is a 1% discount off the flat rate you apply to your turnover, until the day before your first anniversary of becoming registered for VAT. So if the flat rate is 12% of £1,200, in the first year of VAT registration it will be 11% at £132.00, populating Box 1, 3 and 5 & as 'cloudcounter' says, £1,200 income going into Box 6 of the VAT return.
HMRC said they were going to automatically apply EORI to VAT registered customers who had not yet registered and if you go to this link you can find out if they have been registered in this way.
https://ec.europa.eu/taxation_customs/dds2/eos/eori_validation.jsp?Lang=en
In the search box put GB before the clients VAT registration number followed by three zeros 000, press Validation.
It will tell you if they are already set up for it. Normally it also shows the clients name and address on the right, but one of mine asked this the other day (who has not applied) and when I input their number it said they were set up, but it didn't show their company details. Which intimated to me that it was an auto set up by HMRC so I asked the client to phone and check for sure.
The EORI helpline is on 0300 322 7067
Hi Carl
Unfortunately, the client does not use the book keeping package, and would have no idea about posting things online, his knowledge is very limited. This is going to be a problem for customers who are not too savvy with technology.
I get it now. Thanks so much.
I do understand what you are saying and that would be great. I just can't get over the point of if a human has already entered it onto a computer (albeit not in the Sage system), that I am supposed to electronically transfer it into Sage from that system and not physically type it again. Otherwise I could simply take the printed copies of the sales invoices from Excel and type them directly into Sage individually. I will have to ponder on all this, as I just can't get over that one point. Thank you for your response Duggimon
Duggimon, re-typing the figures into a spreadsheet from what I have read is what MTD is making us avoid - not re-typing anything and instead using a template or middle ware to import the information typed the first time. I actually don't think you can re-type an entry. So if the client has typed the information into Excel as an invoice in a template in there. My understanding is that I would have to extract those figures using formula (certainly after the soft landing), to bring the figures in.
The client only inputs the customer invoices into Excel. All the purchases, Direct debits and bank reconciliation is done in Sage.