+ Reply to Thread
Results 1 to 16 of 16

Database & linking other sheets

  1. #1
    Registered User
    Join Date
    03-25-2008
    Posts
    11

    Database & linking other sheets

    Hello all, I am a sales person and have for the past few years just been plonking details of my clients into a basic spreadsheet.

    I also have a separate document that allows me to present figures to a client, and print out an order form. I stick the client details in, save it under another name, and use the original worksheet again for the next lucky victim.

    This means I am plugging in details twice, so what I need to be able to do is to shove in the details into the spreadsheet, and for that to populate a client facing form which I can then use to present - and to be able to repeat that for the next client.

    As you can see, I am not an Excel Diva, and would really appreciate an idiots guide on how I could achieve this.

    Paul

  2. #2
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111
    Quote Originally Posted by Slartibartfast
    Hello all, I am a sales person and have for the past few years just been plonking details of my clients into a basic spreadsheet.

    I also have a separate document that allows me to present figures to a client, and print out an order form. I stick the client details in, save it under another name, and use the original worksheet again for the next lucky victim.

    This means I am plugging in details twice, so what I need to be able to do is to shove in the details into the spreadsheet, and for that to populate a client facing form which I can then use to present - and to be able to repeat that for the next client.

    As you can see, I am not an Excel Diva, and would really appreciate an idiots guide on how I could achieve this.

    Paul

    Welcome !

    I myself have a medium sized database of clients. Sheet one is database of the information I need eg address, phone no's and etc. Then sheet 2 a invoice sheet. To get the information on to sheet 2 by using the vlookup formula. I type in the victim eerr....clients number and the rest of the info follows.

    Then copy the spreadsheet to another book (right click on the tab to copy and move the spreadsheet).

    That's more than like a noobs way of doing it. There are program can handle these things.

  3. #3
    Registered User
    Join Date
    03-25-2008
    Posts
    11
    Hello, and thanks for the welcome.

    I hear what you say there, and thanks for the reply, but I suspect what I am trying to achieve is fairly straightford and would make life so much easier for me, if only I knew how to do it!

    I can get cells immitating other cells, what I cannot do is get them to be consecutive, that is selecting the next line down. I also suspect that the command 'fill' & 'series' comes into play here, possibly linked with 'forms', but I'll be damned if I can figure it out.

    Paul

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Can you upload a zipped example?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Registered User
    Join Date
    03-25-2008
    Posts
    11
    Quote Originally Posted by royUK
    Can you upload a zipped example?
    You make it sound so simple!

    Remember, you are communicating with an Excel neanderthol here, who is has only just realised you spell 'Excel' with one 'L'.

    However, fortunately I learn quickly and have set up and use easy formulas, spread sheets and workbooks, so not as dum as all that.

    Just one question though - how do I upload zipped files? Indeed, how do I zip a file?

    Sorry, but I have to start somewhere!

    Paul

  6. #6
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    Right-click on the file, Send To, Compressed (zipped) Folder.

  7. #7
    Registered User
    Join Date
    03-25-2008
    Posts
    11
    OK, thanks for that, sounds simple anyway - I'll give it a go
    Attached Files Attached Files
    Last edited by Slartibartfast; 03-25-2008 at 05:30 PM.

  8. #8
    Registered User
    Join Date
    03-25-2008
    Posts
    11
    Mmm. Not sure if that worked. It uploaded but I cannot see it. Have I done it wrong?

  9. #9
    Registered User
    Join Date
    03-25-2008
    Posts
    11
    Ahh, think I am getting the hang of this now. This is the file I put my clients into, which then needs to flow that info into the first sheet of my other attachment (Risk Assesment).
    Attached Files Attached Files

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    It's not clear from your example whether you there is only one entry per name or whether you will be adding more details for each Company. This would determine the best approach, if it several entries 7 you want to display sales information to the customers then a Pivottable would be ideal.

    Post back with what you need to extract from the data..

  11. #11
    Registered User
    Join Date
    03-25-2008
    Posts
    11
    At the moment what I do is enter the details into the first spread sheet - name, telephone number etc, & close that sheet. I know have a basic record of the customer details on a spreadsheet.

    Then when I am with a client I open up the other workbook and enter the details again in the 'details' section of the Risk Assessment, (Company, contact name, address, telephone number, mobile, e-mail) which in turn automatically fills the rest of the forms in that worksheet. I can then present the personalised figures to the client, and save it under the clients name - leaving the original blank for the next client.

    The next customer, I open up my first spreadsheet again and the next line down I fill in the details of my new client.

    I now again open the blank second workbook and fill in the new client details in the Risk Assessment page.

    What I am trying to achieve is just to fill the Basic Info worksheet in, and as I type in a client it will fill in the Risk Assessment details ready to be saved - or at least have the ability to press a button and it loads the basic details into the Risk assessment details.

    It would be no hardship to do it the other way around - have the Risk Assessment details fill in the next line on the Customer Basic Info sheet if that is easier (I could then fill in the rest of the basic details later).

    Not sure if I am making myself very clear here - thanks for your patience!

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You should be able to do this with VLOOKUP, see

    http://www.excel-it.com/excel_functions.htm

    If you upload an example of the Form I can add some formulas to show you.

  13. #13
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111
    Hello again,

    I have upload an example of my database to a other form (Tax Invoice).
    Tho its not exactly what Startibartfast wants but it should give an idea of what Startibartfast wants.

    On the the tax Invoice sheet just enter an account number and the rest will happen.

    As far as linking I am in two minds to go about it.

    1. Have your master database and the information sheet in one wookbook, then right click tab on the information sheet then tick copy box then move the sheet to a new wookbook. Auto linked.

    2. Have our master database has one workbook and your information on another workbook (save as a template). Then manual link the two work books.




    Don't hesitate to PM for info
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    03-25-2008
    Posts
    11
    Hello everyone, sorry I have not come back sooner, been away on a very wet & muddy Scout camp!

    That last link RatCat is brilliant, thank you. Having looked at what you have created means that I can just plonk a number into my presentation and up come the related details! So if I just have a lead and no presentation I am not saving a blank presentation, and can still keep a record of the lead.

    One question though, will it allow me to save just the presentation part (or your 'invoice' page) as I put these in a customer history file? Or is that when it would be better to having two separate entities, but linked?


    EDIT:
    Just been thinking about this, does it mean that it will save the information for that particular client I put into the Tax Invoice, once I save? So if I press Account 2, it will come up with that Tax Invoice? Can it?

    Be keen to know the formulas there, not sure I understand them much!
    Last edited by Slartibartfast; 03-29-2008 at 06:00 PM.

  15. #15
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111
    Quote Originally Posted by Slartibartfast
    ..............
    One question though, will it allow me to save just the presentation part (or your 'invoice' page) as I put these in a customer history file? Or is that when it would be better to having two separate entities, but linked?
    There are alot of variable I've found handling the files. I can't answer for you because I don't know level of computer knowledge you have, and most importantly means to the end of your record keeping and presentation part.

    To talk more about, I'm just a PM away.

    Quote Originally Posted by Slartibartfast
    ..............
    EDIT:
    Just been thinking about this, does it mean that it will save the information for that particular client I put into the Tax Invoice, once I save? So if I press Account 2, it will come up with that Tax Invoice? Can it?

    Be keen to know the formulas there, not sure I understand them much!
    It's not that simple.

  16. #16
    Registered User
    Join Date
    03-25-2008
    Posts
    11
    Hah, it never is! I have PM'd BTY.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1