+ Reply to Thread
Results 1 to 5 of 5

Auto-populating a separate sheet based on a dropdown menu

  1. #1
    Registered User
    Join Date
    12-26-2015
    Location
    San Francisco, California
    MS-Off Ver
    2011 for Mac
    Posts
    2

    Question Auto-populating a separate sheet based on a dropdown menu

    Hi. I'm creating a spreadsheet to track my self-employed business expenses. I suspect I need VBA to do what I need to do, but I've not used it before. Here's the setup:

    Sheet 1 is where I track my checking account activity (both expenditures and credits to my account). Columns as follows:
    A: Date of transaction
    B: Description
    C: Amount debited
    D: Amount credited
    E: Running balance
    (F: skipped column)
    G: A dropdown menu where I indicate what kind of business expense an item was, if any (e.g. office supplies, insurance, transportation, etc., as categorized on IRS tax form Schedule C)

    Sheet 2 is set up to be a business expense statement for my taxes. Columns as follows:
    A: Date of transaction
    B: Description
    C and onward: each column matches one of the expense categories in the dropdown menu on Sheet 1

    What I'd like it to do is: whenever I enter an expense on Sheet 1 and choose a business expense category in the dropdown, I'd like the date, description, and expense amount to be automatically populated into Sheet 2, with the expense amount appearing in the correct column for its category. I don't care if those rows on Sheet 1 and Sheet 2 are forever linked; I'm just after one-time population of data, so that I don't have to re-enter every business expense by hand. How do I program that?

    I really appreciate the help! If I get this figured out I'll be glad to share the .xlsx template with the group.

  2. #2
    Forum Contributor
    Join Date
    12-05-2015
    Location
    Akron, OH
    MS-Off Ver
    15.0
    Posts
    424

    Re: Auto-populating a separate sheet based on a dropdown menu

    Provide a sample sheet. A macro may not be required here.

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Auto-populating a separate sheet based on a dropdown menu

    Please have a look at the attached. It uses a macro to rebuild sheet2 from sheet1 every time sheet2 is activated.
    Attached Files Attached Files
    Martin

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Auto-populating a separate sheet based on a dropdown menu

    In all probability, mrice has given you what you want. If not, you need to upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    12-26-2015
    Location
    San Francisco, California
    MS-Off Ver
    2011 for Mac
    Posts
    2

    Re: Auto-populating a separate sheet based on a dropdown menu

    Thanks for the guidance! I should have supplied a sample file in the first place -- still learning how this forum works. Here's one, attached, with some sample data filled in.

    I should note that not every entry in Sheet 1 should populate into Sheet 2, only the ones that are business related. So if nothing is selected in the dropdown in column G, it doesn't need to populate Sheet 2. I also don't need to track credits on Sheet 2, only debits (I track my self-employment income separately).

    I'll take a look and see if I can incorporate mrice's work into this template, but glad for any further guidance. Thanks!Balance sheet.xlsx

+ 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] Populating one sheet with a separate sheets information based on criteria
    By madmoojuice in forum Excel General
    Replies: 3
    Last Post: 11-13-2014, 12:02 PM
  2. [SOLVED] populating a table from a separate sheet based on ranking
    By greggatz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-21-2014, 02:54 PM
  3. Replies: 1
    Last Post: 10-30-2013, 07:34 AM
  4. Replies: 4
    Last Post: 03-26-2013, 07:36 AM
  5. Replies: 4
    Last Post: 05-18-2010, 04:58 PM
  6. Referencing cells on a separate sheet based on pull down menu response
    By Jenrich in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-03-2009, 03:37 PM
  7. populating a dropdown based on choice from a previous dropdown
    By Conor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-09-2006, 03:20 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