Data Validation to Restrict User Input

    I need to make an invoice system to do the following things, if you could help me with any of the points I'd be extremely grateful! I've numbered them so you can let me know which point you are referring too. Thanks for reading this far!


    1. A drop down menu with company names. Another worksheet stores company name, address1, address2, postcode, country. When you choose a company name, the address is automatically added below.


    2. To generate the invoice number, a drop down list (a code for each service) and a number (+1 from the last invoice).


    3. If poss, to automatically name the work sheet by the invoice number.


    4. A new invoice button, that (obviously) opens a new invoice.


    5. An automatically updating date cell.


    Phew! Any help on any point would be great, thank you!:)

    Numbers 1,3,4 & 5 all sound simple enough... 2 i'm not so sure about...


    How about you post up a template of what you want the output to look like with some steerage on the issue you raise in 2


    I'll be around later :guitar:


    [rdv]*[/rdv]

    #1 could be handled using data validation. Create your list and then reference the list as your source in the cell you want the drop down. To get the address, use a vlookup function off of the table and your drop down.


    #5 are you just looking for the function =now() ????
    It gives you current date


    #3 some simple code should make this easy for you...create a save routine that says:
    ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").Range("a1").Value


    Hope this helps you on your way!

    Quote

    Originally posted by Jason_Moffitt
    #5 are you just looking for the function =now() ????
    It gives you current date


    Jason,


    Careful with NOW() it returns Date & Time not just Date - Therefore with autocalculation will be constantly recalcing... :o


    Maybe, for this issue better to use =TODAY() which will only change once every 24 hrs


    (PS - not meant as criticism... just info :wink1: )


    :cheers:

    Ah, yes, =TODAY() very easy, thank you!


    For point 1, I know how to do a drop down box and relate it to a list, but only seem to be able to generate a number, not sure how to do VLOOKUP.


    Thanks for all your help so far!:bsmile:

    Absolutely right Will. Thanks for catching that.


    the Vlookup function will lookup a value in a specified table and return a value from the record in the table where your original value lies. I am sure the function wizard can easily walk you through an example.

    So is your list a list of companies, found elsewhere in the workbook ?


    say you have 3 cols (A:C)


    Co Name : Addr1 : Addr2


    and say 10 company details under


    A2:A11 call "colist" you can then use that for your data validation list OK ?


    A2:C11 call "Lookups"


    Let your company dropdown be in d1 for now & say user selects company name 1


    in cell below type the following
    =VLOOKUP(D1,Lookups,2,FALSE)


    This will lookup the company value, match it with the entry in Col 1 of the "Lookups" range & return the corresponding value in col2 i.e. addr1


    Your VLOOKUP can be on another sheet - this is for illustration...


    Hope you understand .... will post eg later if I'm about... can't do it at work due to Firewall... :cheers:


    Penguin thing: Long story :redface:

    Tell U what..... tis gettin late and i'm needed in the oub :bouncy:


    If this can wait - I'll have a look later if I can or we'll reconvene tomorrow ??


    Up to you - or you can hang out here & see if someone else turns up later...


    So sorry, gotta fly :wink1:

    Having now seen Liverpool get an honourable draw in Glasgow... :lol:


    Here's that example of how to set up points 1 , 3 and the formula for point 5


    Hope this helps



    :cheers:

    Have a look at my example in this earlier posting


    http://ozgrid.com/forum/viewthread.php?tid=905


    Are you trying to create a service invoice e.g for a building service or similar? I have written invoice apps that fill invoices from a macro with set jobs, etc.

    Will-
    OOoooh yes, thanks Will, that's looking good, just got in so going to have a little play with it and let you know how I go. More penguins?!?? I am getting worried about you.


    Roy UK-
    still haven't had a chance to check yours but will be doing so shortly, thanks.:D:cheers:

    Will, would there be a way to save the worksheet rather than the whole workbook, so I can save all my invoices in one workbook? This way I could just have one list of all contacts, and could do sums between the sheets.


    Sorry, I'm really not very good at this!
    :spin:

    Annaeye,


    You could save the invoice sheet within the workbook if you wanted by copying the info to a new sheet (which could be called the invoice number i.e. invoice no 1234 gets saved as Sheet 1234).


    I'd only recommend this if you have VERY FEW invoices.... and if you REALLY need to keep a copy of each invoice as laid out in the template...


    What I would recommend is that the details from your Invoice template get saved to a table once you've produced the invoice.... then you can easily set up a way of viewing that table, say by Supplier, or by Account Code.... well, by any of your saved data criteria really.


    I probably need to know a bit more about what you're eventually wanting to get out of this in terms of what data you need to save etc... XL i'm sure, can give you what you need - we just need to identify it.


    Luckily, I've got a quiet few days till my boss is back, so i'm happy to help within my own capabilities.




    :wink1:

    Wow! I didn't even think of that, much more sensible.


    So I could print the invoice, then save the information within the invoice to a seperate sheet, with columns for inv no, client, service, fee, vat, total? I could even have a column at the end to fill in "sent" and "paid". That would be great!


    Is that what you meant? How's Birmingham today? It's quite sunny in London today, put me in a good mood!:D

    OK... the table route much better - workbook stay smaller! :)


    Best way forward.... Why not set up rest of template with what you want to send to customer... & maybe somehow highlight what info you want saving to the table....


    On a separate sheet, just create the table headers (i'd probably have Inv No as Col A as it can be a unique record identifier).


    Once you've done all that, try emailing it to me at email address in my profile - unfortunately, I work for a bank & can't post files to this site due to a hefty secure firewall.... only at home. So, try the email route & I'll have a look at the process of transferring data from template to table... :D


    And, Birmingham V sunny.... but full of althletes running around (World Champoinships this wkend...)


    Used to work in London for L&G (by Mansion House).... where U?

    OK, will have a go now. Understandable that banks have hefty firewalls really!


    I'm down in Saarf London, Camberwell. Working for a music management company, so we need to invoice record labels for remix fees and royalty advances when we do deals. We just do invoices manually at the moment but it's driving me mad so I'm trying to simplify the system.


    Be back soon...:wink2:

    This might save some time. It can be adapted to suit your Template


    Sub Save_inv()


    Application.ScreenUpdating = False


    TEMPLATE_SHEET = "Form"
    DATABASE_SHEET = "Database"
    COUNT_ROW = 2
    DATABASE_RECORDS = Sheets(DATABASE_SHEET).Range("A1:A10000")


    'To identify the next blank row in the database sheet


    For Each DBRECORD In DATABASE_RECORDS


    If DBRECORD <> "" Then COUNT_ROW = COUNT_ROW + 1


    Next DBRECORD


    'To copy the data from the template to the database


    Sheets(TEMPLATE_SHEET).Select


    'Data Field 1 to database


    Range("B13").Copy
    Sheets(DATABASE_SHEET).Range("A" & COUNT_ROW).PasteSpecial xlPasteValues


    'Data Field 2 to database


    Range("B16").Copy
    Sheets(DATABASE_SHEET).Range("B" & COUNT_ROW).PasteSpecial xlPasteValues


    'Data Field 3 to database


    Range("LastName").Copy
    Sheets(DATABASE_SHEET).Range("C" & COUNT_ROW).PasteSpecial xlPasteValues


    'Data Field 4 to database


    Range("Net").Copy
    Sheets(DATABASE_SHEET).Range("D" & COUNT_ROW).PasteSpecial xlPasteValues


    'Data Field 5 to database


    Range("VAT").Copy
    Sheets(DATABASE_SHEET).Range("E" & COUNT_ROW).PasteSpecial xlPasteValues


    'Data Field 4 to database


    Range("Total").Copy
    Sheets(DATABASE_SHEET).Range("F" & COUNT_ROW).PasteSpecial xlPasteValues


    'Check Data has posted correctly


    Worksheets("Data").Activate
    Range("A1").Select




    'To sort the database on basis of say date - column D in Database


    Sheets(DATABASE_SHEET).Select
    Range("A1:D10000").Select
    Selection.Sort KEY1:=Sheets(DATABASE_SHEET).Range("D1"), ORDER1:=xlAscending, HEADER:=xlYes, ORDERCUSTOM:=1, MatchCase:=False, Orientation:=xlTopToBottom


    Sheets(TEMPLATE_SHEET).Select
    Range("C1").Select


    ActiveWorkbook.Save


    End Sub


    This has a worksheet set up as an Invoice template called "Form" and a record sheet called "Database". I usually attach this macro to a button so that I can record it when I am satisfied with the end result. Alternatively it could be in the Before_Print event .

Participate now!

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

Register Yourself Login