Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Washington State (we moved)
    Posts
    16
    Thanks
    3
    Thanked 0 Times in 0 Posts

    How to get dynamic form data into a static sheet

    Hi All,

    I am trying to accomplish something using Excel, which would be more appropriatly done in Access, which I do not have. That said, I do not even know if Excel can do what is needed, but I figure that if it can someone here will know.

    There are two "forms", one is a single sheet that various information (date, time, name, address, etc.) is entered into when a call is recieved. We will label it the Call Sheet. Once the information is entered in the Call Sheet, it is printed and sent on its merry way never to be delt with again by the computer person. This form is cleared after being printed, and awaits the next call for new information to be entered.

    The second form is an aggregate of each of the Call Sheet forms as they are completed. We will label it the Call Record. As each instance of the Call Sheet is done, the information needs to be copied into the next empty row of the Call Record. When the Call Sheet is cleared, the entry on the Call Record needs to remain in place, not disappear.

    Accomplishing the first part of this is no problem, one Call Sheet to one Call Record row... up to the point where the Call Sheet is cleared. The problem I am stumped over is getting the information to stay in the Call Record when the Call Sheet is cleared, and then move to the next empty row for the next Call Sheet entry.

    Any help is greatly appreciated.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Hello and welcome to the lounge as a poster.

    I'm a little confused by your use of the term Form for the Call Record. It seems to me that this would just be a spreadsheet page not a Form. When I use the word Form in Excel I'm usually referring to a VBA based Form created in the Visual Basic Editor {VBE}. If, however, you are using the Builtin Form tool {Data, Form in Excel 2003 and prior} in Excel that takes the headings from a table and allows you to navigate the rows of the table and add/delete/search it has limited capabilities and when you clear a record you clear the spreadsheet data associated with it {see attached}. Could you please elaborate on which type of form you are using.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If I understand what you are after I envision 2 worksheets (not using a form at all): a worksheet where you enter the call information (the call sheet) and when that is done, a button would be pressed calling a macro which would copy the items into the appropriate columns in the next row of the call record worksheet, and then clear the items in the call sheet

    It would help if you had a sample file with a setup call sheet and call record as you envision them if you need help with a macro.

    [It could also be done with a VBA form as a call sheet and a worksheet to collect the data, but the 2 worksheet model would be simpler to create...]


    Steve

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Washington State (we moved)
    Posts
    16
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Call and Record Sheets

    Hi, and thanks Steve and RetiredGeek for the replys. I am late getting back to you due to a long work day, but attached (I hope) is a workbook containing two sheets I have been playing with in various itenerations. They are not from the same sets, but are the closest to what we are looking for that are not too cluttered up with previously tried formulas, though I would probably re-add drop-downs on certain of the Call Sheet's cells that would pull Dealer, Driver, and Advisor names from a third sheet I had containing specific lists for them.

    I made the Call Sheet cells large enough that we can also print a blank copy of it for any times someone may have to fill one out by hand and enter the info later.

    I hope this helps.

    Many thanks...

    DC.
    Attached Files Attached Files

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    DC,

    From look at your workbook I would say what you need is a button, or two, with a macro(s) attached to copy the information from the appropriate area to the "Call Record" sheet, print the area, and then erase/reset the data from the "Call Sheet" sheet.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Some code like this perhaps:

    Code:
    Option Explicit
    Sub UpdateRecord()
      Dim wSheet As Worksheet
      Dim wRecord As Worksheet
      Dim lRow As Long
      
      Set wSheet = Worksheets("Call Sheet")
      Set wRecord = Worksheets("Call Record")
      With wRecord
        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(lRow, 1) = wSheet.Range("B2")
        .Cells(lRow, 2) = wSheet.Range("B4")
        .Cells(lRow, 3) = wSheet.Range("A7") '?? Not sure of cell
        .Cells(lRow, 4) = wSheet.Range("B7") '?? Not sure of cell
        .Cells(lRow, 5) = wSheet.Range("E8")
        .Cells(lRow, 6) = wSheet.Range("E10")
        .Cells(lRow, 7) = wSheet.Range("E13")
        .Cells(lRow, 8) = wSheet.Range("E15")
        .Cells(lRow, 9) = wSheet.Range("E2")
        .Cells(lRow, 10) = wSheet.Range("E4")
        .Cells(lRow, 11) = wSheet.Range("G2") 'or G5??
      End With
      With wSheet
        .Range("$A$1:$G$42").PrintOut
        .Range("E2:E5,G2:G5,E8:G8,E10:G11,E13:G13,E15:G15,E17:G19,B8:B19").ClearContents
        .Range("E25:E28,G25:G28,E31:G31,E33:G34,E36:G36,E38:G38,E40:G42,B31:B42").ClearContents
      End With
      
      Set wSheet = Nothing
      Set wRecord = Nothing
     
    End Sub
    I was not sure exactly what cells went into what columns, but you should be able to adapt the idea if the cells I picked were wrong or you will add other columns to the data...

    Steve

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    Tropicalroo (2011-12-09)

  8. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Washington State (we moved)
    Posts
    16
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Outstanding!

    This was just what I was looking for, and way more than I actually expected for Excel. Having used mostly dbase and Access for this type of thing in a previous life, I guess I just never got around to seeing what could be done in Excel after I retired.

    I have attached the latest version so you can see where I've gone with it so far.

    Now I have to go find a decent online source for coding syntax in this so I can start doing more of it myself without having to bother others.

    Your help is thoroughly appreciated.

    DC
    Attached Files Attached Files

Posting Permissions

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