Forum Discussion
Add value to column based on dropdownlist value.
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.
I appreciate the input. With this specific sheet I am not interesting in tracking individual items, only the overall budget expenditures.
I ended up getting it working with a macro, not my cleanest work but it is functional and I will continue to refine it until it doesn't look like a spaghetti mess.
I did however look at transitioning to an actual table, will likely move forward with that and just create a new style as I don't care for the ones available.
Thanks
