Drop-down list exception

morrisco

New Member
Joined
Aug 5, 2011
Messages
3
I posted a thread earlier about a balance sheet and now I have another question.

I have an expenses table that dispalys all the expenses that happened during a given year, the year is displayed on the formula as a reference to a cell that contains a drop down list where you pick the year. So if I pick 2010 on this drop down list the expenses table displays all the expenses of that happened during 2010, if I pick 2011 it dispalys the transactions of 2011, and so on.

So what I wanted to know if there is some sort of command that I can add to the data source of the drop down list that works as a "all of the above" option. So that if I pick that all of the above option the table will display all the expenses. Is this possible or what is the closest thing to it?

I think it is important to clarify that the expenses are chosen from a transactions table using a SUMIFS formula that checks if the transactions belong to the year chosen in the drop down list. So what I need to have is an option in the drop down list that makes the SUMIFS formula choose transactions from any year.

Can this be done?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
You can use expressions like ">" in SUMIFS etc formulas, for example you can do:

Code:
=SUMIF(CriteriaRange, ">=" & 2005, SumRange)

to sum Values for year >= 2005.

Also, if you are currently using VALIDATION for you dropdown list you may also want to check out he LISTBOX and COMBOBOX controls available in the DEVELOPER tab.
 
Last edited:
Upvote 0
Maybe something like

Include (all) as the last element of your validation list and reset the validation range to include this.

You validation-list wiil look like

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>List</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>2002</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>2003</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>2004</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>(all)</TD></TR></TBODY></TABLE>

Assuming E2 as the validated-cell, change your formula to something like

=SUMIF(A2:A10,IF(E2="(all)",">0",E2),C2:C10)

where A2:A10 (numbers) is the criteria-range and C2:C10 the range to be summed.

If A2:A10 has text-values you can change the formula to
=SUMIF(A2:A10,IF(E2="(all)","*",E2),C2:C10)

HTH

M.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top