Creating Drop Down Lists for Data Entry

%3CLINGO-SUB%20id%3D%22lingo-sub-1806018%22%20slang%3D%22en-US%22%3ECreating%20Drop%20Down%20Lists%20for%20Data%20Entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1806018%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20new%20to%20excel%2C%20and%20am%20creating%20a%20budget%20workbook.%20I%20have%20sheets%20for%20each%20month%2C%20with%20entries%20for%20different%20expenses.%20These%20sheets%20all%20contain%20various%20formulas%20to%20automatically%20calculate%20total%20%24%20spent%20on%20each%20item%2C%20compared%20to%20income.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20am%20trying%20to%20learn%20is%20how%20to%20create%20a%20page%20where%20I%20can%20select%20from%20drop%20down%20menus%20the%20month%2C%20category%20(debt%2C%20utilities%2C%20expenses%2C%20etc.)%2C%20type%20(credit%20card%2C%20groceries%2C%20etc.)%2C%20and%20then%20type%20the%20amount%20spent%2C%20and%20that%20value%20is%20automatically%20added%20to%20the%20corresponding%20sheet%20and%20cell.%26nbsp%3B%20For%20example%2C%20if%20you%20purchased%20a%20red%20bull%2C%20you%20can%20selct%20the%20October%2C%20Expenses%2C%20Junk%20food%2C%20from%20the%20drop%20downs%2C%20and%20then%20type%202.14.%20Then%20this%20amount%20is%20automatically%20added%20to%20the%20October%20sheet%2C%20under%20the%20expenses%20category%2C%20and%20%22junk%20food%22%20type%2C%20where%20it%20originally%20showed%20%2410.00%2C%20and%20now%20shows%20%2412.14%20after%20the%20entry.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20searching%20the%20internet%20for%20this%20solution%2C%20and%20have%20seen%20a%20lot%20of%20information%20on%20creating%20a%20data%20table%20and%20data%20validation%2C%20but%20have%20not%20found%20anything%20clear%20on%20my%20desired%20outcome.%20Any%20help%20or%20direction%20to%20tutorials%20will%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1806018%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1806963%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20Drop%20Down%20Lists%20for%20Data%20Entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1806963%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F841348%22%20target%3D%22_blank%22%3E%40BoCFes%3C%2FA%3E%26nbsp%3BConsider%20collecting%20all%20your%20transactions%20in%20one%20single%20(structured)%20table%20and%20create%20any%20summary%20you%20want%20from%20it%2C%20using%20one%20or%20more%20pivot%20tables%2C%20on%20demand%20and%20letting%20Excel%20do%20what%20it%20does%20best.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20need%20to%20keep%20separate%20tables%20for%20each%20month.%20And%20as%20you%20probably%20already%20figured%20out%20from%20your%20internet%20searches%2C%20you%20can%20create%20lists%20that%20contain%20all%20the%20options%20for%20your%20drop-downs%20(data%20validation)%20in%20the%20transactions%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20need%20for%20VBA%20coding%20either.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1808777%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20Drop%20Down%20Lists%20for%20Data%20Entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1808777%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20That's%20a%20very%20good%20point%2C%20and%20much%20simpler.%20Thank%20you%20for%20the%20advice!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am new to excel, and am creating a budget workbook. I have sheets for each month, with entries for different expenses. These sheets all contain various formulas to automatically calculate total $ spent on each item, compared to income. 

 

What I am trying to learn is how to create a page where I can select from drop down menus the month, category (debt, utilities, expenses, etc.), type (credit card, groceries, etc.), and then type the amount spent, and that value is automatically added to the corresponding sheet and cell.  For example, if you purchased a red bull, you can selct the October, Expenses, Junk food, from the drop downs, and then type 2.14. Then this amount is automatically added to the October sheet, under the expenses category, and "junk food" type, where it originally showed $10.00, and now shows $12.14 after the entry. 

 

I have been searching the internet for this solution, and have seen a lot of information on creating a data table and data validation, but have not found anything clear on my desired outcome. Any help or direction to tutorials will be greatly appreciated.

 

Thank you so much.

2 Replies

@BoCFes Consider collecting all your transactions in one single (structured) table and create any summary you want from it, using one or more pivot tables, on demand and letting Excel do what it does best.

 

No need to keep separate tables for each month. And as you probably already figured out from your internet searches, you can create lists that contain all the options for your drop-downs (data validation) in the transactions table.

 

No need for VBA coding either.

@Riny_van_Eekelen  That's a very good point, and much simpler. Thank you for the advice!