Copying certain cells from each row to another sheet for invoice

    Re: Copying certain cells from each row to another sheet for invoice


    Hi mate,


    I have attached the updated file. I have added three new columns, two for actual use and one blank for future use!


    This new information does not have to transfer to the invoice sheet! :)


    I have noticed that the labour charges do not transfer to the invoice sheet either. ( you probably are on to this and i'm jumping the gun bringing it to your attention!)


    Is there a possibility to add a button on the sheet 3 (invoice) that clears the materials and labour chargers? So that when i re open the file rather than manually delete this info it can be wiped in one fell swoop?


    Mucho gracias
    Michael

    Re: Copying certain cells from each row to another sheet for invoice


    Hi Mike


    Where do you want the labour charges from the Calcs sheet to appear. I thought by putting the Item desc, quantity, Unit Price and Total would automatically have these details from the Calcs sheet populate in Rows 36 and 40 of the Template sheet?


    Just upload a fresh workbook with what the data sheet has for import into the Template sheet, what the calscs sheet has and what you expect to appear in the Template sheet. I should be able to work it out without you going into any detail. That would be a big help.


    Take care


    Smallman

    Re: Copying certain cells from each row to another sheet for invoice


    Hey,


    All sounds good from how you described transferring the data to the invoice sheet. I have all the final information to hand now including prices, stock codes etc. i will get on with it now and will have the finished workbook to you this evening.


    Thanks again mate!

    Re: Copying certain cells from each row to another sheet for invoice


    Hi Mike


    Reading over my post I have to appologise. I ment to say populating the Invoice sheet from the Data tab is all that is required. Your totals on the Invoice sheet work out all the sub total and total data. So there is no need for the Calcs sheet at all. Is this correct. If not i want to know where to put the Calcs sheet data. I don't think that sheet should even be in the model. Two sheets, Data and Template. Data populates Template. Job done. This is my current understanding.


    Take care


    Smallman

    Re: Copying certain cells from each row to another sheet for invoice


    Hi Smallman,


    Yeah lets get rid of the calcs sheet altogether. So just the data and invoice template sheets.


    Thanks
    Mike

    Re: Copying certain cells from each row to another sheet for invoice


    Hi Mike


    Here is the latest Iteration of your model. I have adjusted the code accordingly. As a bit of background the two Arrays are psudo column number holders. These are the columns being copied (Ar) and the columns in the Template sheet which are being pasted to (Arr). So if your model changes, you simply change the column references in the array.


    Let me know how this is looking. Oh I put a clearcontents line in the create Invoice process which clears your Template sheet prior to populating it. This will save you manually doing this. Having things happen on save is a bit random, as you can clear data you really wanted to keep by mistake.


    Have you thought about a scenario where you have more than 17 line items in the Template sheet? Could it happen?


    Anyways, later.


    Smallman


    Re: Copying certain cells from each row to another sheet for invoice


    There is a possibility of more than 17 items transfering yes. Its good that someone is on the ball! maybe decrease the size of the cells or have the invoice template to print on to 1 or more pages??


    Thanks!
    Mike

    Re: Copying certain cells from each row to another sheet for invoice


    This works perfectly! would i be good to insert another column next to Mikes' new column? or maybe 2 new ones?


    Cheers
    mike

    Re: Copying certain cells from each row to another sheet for invoice


    Mike


    Is there a possibility that you want to run this project for multiple clients. You could spit out multiple templates as the output. This would ensure you don't have to keep re running the process. Just run it once produce data for all your clients have 20 - 30 sheets (files) output to your hard drive. This is how I see you have the model run itself. No work other than maintaining the data tab then. Just my thoughts. Decreasing the size of the cells won't reduce the amount of lines to be input. Just adding additional lines in the model between rows 19 and 35 on the template sheet will cover off more entries. Should be able to add at least another 20 rows.


    Take care


    Smallman

    Re: Copying certain cells from each row to another sheet for invoice


    We are running the company in partnership with our sales team. We will only be carrying out work for them, for the first few months. I'm going to drive the company looking for new work, and new clients once we have a solid foundation to build on. :) your idea of having multiple templates is a great idea and maybe I could look at in more depth once we have this awesome tool working!!!


    Thanks
    Mike

    Re: Copying certain cells from each row to another sheet for invoice


    Hey mate,
    Ive updated all the stock in the column A6:A6<
    The stock numbers have been added in B6:B6<
    And the suppliers details in C6:C6<
    The function of the table doesnt seem to work correctly. I tried to update stock and create invoice with no luck!
    The data entry sheet no.1 needs tiding up so all the colouring is uniform, I'm not that fussed what colours as long as it professional :) if it is easy to do i'll find out how to do it! (to alleviate you work load haha) :)
    I really feel we are actually edging near a finished product!!!!!!!! :)
    i was thinking of offering this work book to my old boss who promoted me recently.( he runs a similar sized installation team under the same group company that we work for, and he is a director of my new company!) He is scared of computers and anything that looks remotely complicated (apart from simple working excel programs) he will shy away from.
    I really want to impress him with my initiative, to come up with this concept and I know with all my heart it will make both installation companies run so much more efficiently.
    Soooo i was think of shading the manual input colums the same colour ( columns A B C D E ) and locking all the others in the work book so they can not be edited. (please dont lock the invoice template as this needs to be largely edited from job to job for the time being!)
    This is important, if someone who is unfamiliar with the formulae of the workbook and ends up deleting an entry, the whole program doesnt go to ****!! Plus the colouring aspect will be a visual reminder on what values are available for them to edit.( to update costs, add new items etc)


    I have a problme uploading the latest excel workbook on this thread, getting an error goes along the lines of 500[IOErrorEventtype="ioError"bubbles=flase cancelable=false eventPhase=2text=Error#2038" arrggghhh been trying for an hour with still no luck. Can i send it to your email?? frustrating!


    I hope this makes sense :) its been a long day in the office!
    HUGE thanks
    Mike

    Re: Copying certain cells from each row to another sheet for invoice


    Hey mate,


    Ive updated all the stock in the column A6:A6<


    The stock numbers have been added in B6:B6<


    And the suppliers details in C6:C6<


    The function of the table doesnt seem to work correctly. I tried to update stock and create invoice with no luck!


    The data entry sheet no.1 needs tiding up so all the colouring is uniform, I'm not that fussed what colours as long as it professional :) if it is easy to do i'll find out how to do it! (to alleviate you work load haha) :)


    I really feel we are actually edging near a finished product!!!!!!!! :)


    i was thinking of offering this work book to my old boss who promoted me recently.( he runs a similar sized installation team under the same group company that we work for, and he is a director of my new company!) He is scared of computers and anything that looks remotely complicated (apart from simple working excel programs) he will shy away from.


    I really want to impress him with my initiative, to come up with this concept and I know with all my heart it will make both installation companies run so much more efficiently.


    Soooo i was think of shading the manual input colums the same colour ( columns A B C D E ) and locking all the others in the work book so they can not be edited. (please dont lock the invoice template as this needs to be largely edited from job to job for the time being!)


    This is important, if someone who is unfamiliar with the formulae of the workbook and ends up deleting an entry, the whole program doesnt go to ****!! Plus the colouring aspect will be a visual reminder on what values are available for them to edit.( to update costs, add new items etc)



    I hope this makes sense :) its been a long day in the office!


    HUGE thanks


    Mike

    Re: Copying certain cells from each row to another sheet for invoice


    Sorry lost in tranlastion again! i thought you wanted just the data inputted for reference! i will input it into the latest file :)


    Thanks Mike

    Re: Copying certain cells from each row to another sheet for invoice


    I've just updated it and still no luck. I may have screwed up the code?? i didnt re-write it though.


    Thanks
    Mike

    Re: Copying certain cells from each row to another sheet for invoice


    Im still struggling uploading the file as well. Sorry to be a pain in the backside!!

    Re: Copying certain cells from each row to another sheet for invoice


    Found the issue with the latest file. When i press update stock, it resets "Mikes' new column" and not the QTY column.


    But i have added another column since you sent me the latest file. I have tried to upload this but for some reason it is playing up. Maybe its my work server? i'll try when i get home.


    Cheers
    Mike

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login