Drop-down list exception

[morrisco]

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?

 

[Hamburgler]

You can use expressions like ">" in SUMIFS etc formulas, for example you can do:

=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.

 

[Marcelo Branco]

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.