sub boxes

Copper Contributor

i use excel to keep track of my bills and what i spend. im trying to figure out how to get all my items i spend for a category in a drop down sub folder where i can enter each item and what i spent imputed and then it auto add them on the main cell 

 

example  right now on my spread sheet i have a cell where i add everything i spend on gas and enter the amount.  i want to create a sub folder on that gas cell where i can enter every gas transaction and what i spent on that gas transaction and have it add the sum up and to reflect on the main gas cell.  

 

can anyone help me with this if it is possible 

13 Replies

@uber719 

I'm a little confused - do you mean creating a dropdown of main expense categories and having the figures automatically sum by category?:

adversi_0-1605556796596.png

 

@uber719 

If I may ask, what kind of drop-down menu do you need?

Dropdown with hyperlink? ... with text options? ... with VBA? ... with colors?...etc.

 

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture.

Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

You could get a precise solution much faster with a file (w/out sensitive data).

This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

* Knowledge of Excel version and the operating system are a must if you want to get a reasonable solution (Example: office version e.g. 2016 or 2019 or 365 web or 365 pro, etc) and your operating system (e.g. Win10 (2004), Win 10 (1903), Mac, etc.).

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

@adversi yes something like that. i want the highlighted area to sum up what I put into the drop down. so this is the amount I spend on gas. In the drop down I want to see where I spent the money for gas and the total. so I would input shell in one cell then the total in the next cell and so on for the next few stations I go to as shown below in example 1.  the sum of the amounts would then be displayed in example 2 in the highlighted area.  hopefully this helps

 

example 1

2excell help.JPG

 

 

example 2

excell help.JPG

@NikolinoDE 

I want the highlighted area to sum up what I put into the drop down. so this is the amount I spend on gas. In the drop down I want to see where I spent the money for gas and the total. so I would input shell in one cell then the total in the next cell and so on for the next few stations I go to as shown below in example 1.  the sum of the amounts would then be displayed in example 2 in the highlighted area.  hopefully this helps

 

example 1

2excell help.JPG

 

 

example 2

excell help.JPG

@uber719 

Thanks for clarifying. You will need to create a SUMIF relation.

 

For example 1, Column C or the last column of your expenses should have the dropdown option.

You can do this by:

1.  Select all the cells in Column C that you need and go to the Data section of the ribbon and select Data Validation.

adversi_0-1605799871936.png

 

2. From there you change the Allow: option to List and under Source: you can select all the options you have listed under the 15th of the month column in your Example 2 screenshot.

adversi_1-1605799930960.png

 

3. Under your Actual Cost column in Example 2, your SUMIF formula should be like this (used placeholders to give a better detail of what the formula needs):

More information can be read here.

=SUMIF(Example1ColumnC,Example2B38,Example1ColumnB)

 

@adversi ok that isnt what im lookng for.  i want a drop down box that i can fill out like example 1 and have the total from that populate in the cell of example 2.  from what i can see the solution you provided doesnt do that 

@uber719 

What needs to be created is a standardized category list that can be summed dynamically. See the example below following the steps from the previous response.

 

adversi_0-1606221433547.png

 

Can you confirm if this is what you're looking for or is there an element missing?

 

@adversi no this is not what i want either. close tho. i want the gas trips hidden in a drop down that i can click on. so in the cell that says gas there would be a + sign in one of the corners or some kind of button that would expand a window where i could input the fillups and the charge then have it sum it up and display the total in the gas cell 

I can only repeat myself again.
Add a file (without sensitive data) and explain your plans on the basis of this file. Please no photos, especially with Excel these do not help in most cases, sometimes even the opposite.
Let us know which version of Excel and which operating system you have.

You probably don't need a dropdown menu, rather see a pivot table that suits your project.
But first, please read the above information.

Thank you for your understanding and patience

Nikolino
I know I don't know anything (Socrates)

@uber719 

Thank you for clarifying - I have a better understanding of you want.

To achieve the popout window to do data entry, it would require VBA/coding and some other complicated methods.

 

I think an important question to ask is what the file is trying to accomplish rather than how. For instance, do you want to track if your monthly expenses exceed your expected budget? Do you want track costs Month over Month or Seasonally?

 

In my experience, keeping one tab dedicated to raw data that you can download from your bank statements that can then be manipulated and analyzed in many different ways is more preferable than manually typing each expense by category.

@NikolinoDE ok added the excel file

@uber719 well i thought i did how do i add it 

@adversi you telling me there is no tool i can use that will do this simple feature i want to do