Jump to content


Photo

How to transfer Invoices into summary sheet automatically

pivot table concatenate LEFT RIGHT MID

  • Please log in to reply
6 replies to this topic

#1 Masud Siddique

Masud Siddique

    Newbie

  • Members
  • Pip
  • 4 posts

Posted 26 December 2016 - 06:59 AM

Hello

 

We have an Excel invoice template we used for our everyday sells invoices and at the end of the day we transfer all the invoices whatever we made on the particular date to the  summary sheet in order to make the record of everyday sells. Now my question is if there any XL features or formula by which we can transfer all the invoices automatically in stead of transfer them manually to the summary sheet. Pls note that we use the same template for invoice and summary sheet. 

 

I have another question that is when we made any invoice we give a number and for the next invoice we need to check the previous invoice in order to know the next number. Now is there any formula by which we can avoid this hassle to look every time the previous invoice for the next invoice no. 

 

Thanks

 

 



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 831 posts

Posted 27 December 2016 - 04:38 PM

Hi Masud,

 

From your description, it sounds like you might find it easier to store all of your invoices in a single workbook and use a pivot table to create the summaries you need. You can then refresh the pivot table whenever you add more invoices.

 

You can see how to create a pivot table in the video lesson: Expert Skills Lesson 5-1 Create a pivot table and see a lot more about pivot tables in the rest of Expert Skills Session 5.

 

Regarding invoice numbers, if your numbers are numeric you should be able to do this by using a simple formula like =A1+1 (where A1 is the location of the previous invoice number). If there is a combination of letters and numbers you should be able to achieve this by using the concatenation operator, and possible the LEFT, RIGHT and MID functions. You can see how to use all of these in the following video tutorials:

Expert Skills Lesson 3-18 Concatenate strings using the concatenation operator

Expert Skills Lesson 3-20 Extract text from fixed width strings using the LEFT, RIGHT and MID functions

 

I hope this is helpful information, but please feel free to reply if you have any further questions.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Masud Siddique

Masud Siddique

    Newbie

  • Members
  • Pip
  • 4 posts

Posted 28 December 2016 - 06:17 AM

Hi Jonathan 

 

Thank you so much for your reply. For your better understanding here I attached a copy of Invoice and Summary sheet. If you look at the invoice there are two part in the invoice. The top part we transfer in the summary sheet and the bottom part ( red marking) we give to our customer, Then if you look at the summary sheet, at the end of the day we transfer all the invoices we made in a particular day. Now we transfer the invoices manually I mean we copy and paste all the rows and columns to  summary sheet which is really time consuming and an extra work at the end of the day. 

 

However I want to know the method by which I can transfer the invoices whenever I make it. I don't know whether I can do it by Pivot table. I shall try to do it by Pivot table. In the mean time if you have any better idea in your mind pls feel free to advise me.

 

Thanks.

Attached Files



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 831 posts

Posted 28 December 2016 - 09:34 PM

Hi Masud,

 

After taking a look at your examples I can see you're trying to use Excel as an invoicing system, which isn't really what Excel was designed to do.

 

Most commercial invoicing systems store their invoices in a database, which allows them to be easily retrieved and summarized. Excel doesn't have the ability to store and retrieve large amounts of data in this way, but it's an ideal tool for analyzing data after it has been extracted from a database.

 

Excel doesn't have the ability to automatically copy data from one worksheet into another, so copying and pasting is probably the best way to do this if you continue to use Excel. If it's becoming too cumbersome, you might want to consider moving to an Access database or a different piece of software that's designed for invoicing.

 

I'm sorry I don't have an easier solution; I think you may be reaching the limits of what it's possible to do easily in Excel.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 Masud Siddique

Masud Siddique

    Newbie

  • Members
  • Pip
  • 4 posts

Posted 29 December 2016 - 06:04 PM

Hi Jonathan

 

Thanks once again for your prompt reply. I understand your point I'll discuss with my boss regarding your opinion. However I heard that it is possible to transfer the data from one workbook to another by using excel VBA. I am wondering if you have any idea how to transfer data by using excel VBA.

 

Thanks. 



#6 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 831 posts

Posted 29 December 2016 - 08:28 PM

Hi Masud,

 

VBA allows you to add new features to Excel and there is no limit to what you could do with it in theory. That said, using VBA means you are extending Excel rather than using it for its intended purpose. Essentially, using VBA means you are developing new software.

 

If you find yourself using VBA to develop new software, you should definitely consider your options. You're very likely to find that a purpose-built accounting package or Access database will be much easier to use and more cost effective.

 

If there isn't an existing software package that will serve your needs and you do need to develop new software VBA is still unlikely to be the best answer. For developing new software you might want to consider Visual Studio and the C# or Visual Basic languages. Visual Studio is a much more versatile development environment.

 

You can find more about this here.

 

Unfortunately we can't offer VBA help on these forums as VBA goes beyond the scope of Excel's features.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#7 Masud Siddique

Masud Siddique

    Newbie

  • Members
  • Pip
  • 4 posts

Posted 29 December 2016 - 08:42 PM

Many thanks for the info. 







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users