+ Reply to Thread
Results 1 to 3 of 3

Input form moves data to master form for cash count sheet

  1. #1
    Registered User
    Join Date
    01-28-2017
    Location
    Salem, Oregon
    MS-Off Ver
    2010
    Posts
    2

    Input form moves data to master form for cash count sheet

    I have a project that requires a large macro that is beyond my capabilities. I provided an attachment of what I am looking to accomplish with the macros. "Input" is where a person would enter the "B1" name on a check or cash receipt; "B2" check # and then "B5" is all of the options that a person can fill in the dollar amount of the giving per item. "G26" is where any miscellaneous items not already listed would be inputted and their $$ amount. "A28" is the macro "enter" button which would push the data to "master" and then wipe "input" clear for the next check or cash receipt. On "Master" you can see how I have mocked a few items like "jane" gave to multiple items that were on "input" as well as building supplies which was an add-in on "other". On "Master" I only want to see those items that have been used, otherwise I could create a macro that can pull to each and every item on a pre-generated list. That is where I am drawing a blank on where to even start.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,446

    Re: Input form moves data to master form for cash count sheet

    This was a relatively easy project to do. I took some liberties with “low hanging fruit” to improve the sheet.

    First, I unlocked all the shaded cells. Then I protected the sheet using Review -> Protect Sheet. This will prevent you from entering in data where it doesn’t belong or clobbering the program names. There is no password on this. If you really want to change something you can unprotect and reprotect the sheet. It will prevent you from changing things accidentally.

    If you do need to edit the sheet to add or delete items, pay attention to which cells are locked or unlocked. The program looks down to row 32 in columns B, E and H, so you have some room for growth.

    I added some data validation. You can only enter in dollar amounts greater than zero.

    I do a check to make sure you have cells B1 and B2 filled in before committing the records.

    I added a Running Total in cell G1. This total keeps track of the dollar amounts entered into the yellow cells. This way you can check to see that the distribution equals the total on the check.

    When you commit a record, it is written to the data page. This page is set up so the data is in what is called a normalized form. Normalized data work very well with pivot tables and other formulas. I have two of them set up on the master sheet. I suggest you read up on and play with pivot tables.

    The first one shows donations by item. The second one shows donations by person.

    The data are in an Excel table. Unless you want to know more about them, I’ll just tell you this: to delete records select cells in the rows you want to delete and right click. A menu appears and one of the options is Delete. One of its sub-option is Table Rows.

    I left the sample data in there so you can see what kind of data is being tracked and to give you something to play with if you want to get some experience with pivot tables.

    I suggest you play with the form for a while to make sure it does what you want, and now that you know what can be done, to see if you want any other improvements.

    I don’t know if you would like to track a date with each record. That might come in handy for slicing and dicing the data.

    Also you might think about doing First and Last names so you can sort by name better or maybe you are already entering the names that way. There is the possibility to look up names by referencing another list. If you have a list of parishioners, it can be included and you can use a drop down list to select people.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    01-28-2017
    Location
    Salem, Oregon
    MS-Off Ver
    2010
    Posts
    2

    Re: Input form moves data to master form for cash count sheet

    Thank you so much! I am very familiar with pivot tables, but did not even think to utilize a pivot table to aggregate the data. I will try this out and let you know how it does.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to create new sheet from template & rename, then write data from input form to it
    By Nickvi1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-01-2015, 07:42 AM
  2. Macro to transfer data from Input sheet to summary form
    By CA_needing_help in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-04-2013, 03:14 AM
  3. [SOLVED] Particular data taken out form input sheet to desire sheet by macro
    By nur2544 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-23-2013, 01:52 PM
  4. Copy Data from a fixed form of data sheet into a master excel sheet.....
    By sirimhk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-18-2012, 08:48 AM
  5. user form listbox value = workbook sheet and data input
    By lex8058 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-29-2012, 06:32 PM
  6. Looping a data input form based on form option
    By bologne in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 04-17-2011, 03:06 AM
  7. [SOLVED] Automatically Display a data input form when active sheet changes
    By Duncan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2005, 05:06 PM

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