I have a simple spread sheet to track budgeted items for a job.
I have a column for cost codes, allowance, allowance used, allowance remaining.
I created a dropdownlist with the values from the cost codes column and a field to enter the amount I would like to add to allowance used cell of that specific cost code but am unsure how to make it happen.
I am able to select the cost code from the dropdown and enter a value, but I dont know how to go about looking up the row based on the dropdownlist value and then adding the amount from B13 to the D column of the specified row in the dropdown, in this example it would be D4.
What methods can I use to accomplish this goal? I would like to either add a button to complete the data entry or have it happen upon hitting enter. I would also like to clear the dropdownlist and the amount column upon completion.
Thank you.
@rdwmatt Allow me to make a few observations.
1) You have created a structured Excel table A4:E10 containing an empty column and a few empty rows. That's not how such tables are intended to be set-up.
2) Then you want to enter a single transaction in A13:B13 (code and amount) and have it added into the table above. This will require VBA coding and leave you with no possibility to really track spending by code whatsoever.
Better to create a structured table with at least four columns (Date, Code, Amount, Description) and perhaps more if relevant for any future analysis. No empty rows and columns that may make it look nicer. Just a 'boring' list of chronological allowances and expenses.
Then use one of many built-in tools / functions to summarise that transaction table. I'd start with a Pivot Table to avoid having to write potentially complicated formulas. Keep adding transactions to the table and press Refresh All on the Data ribbon to update the pivot table.
Example attached.