Conditional Sum to match against drop down list

[wanwan]

Hi,

I must admit I am a bit of a novice at this, but am trying to learn.

Basically I have a workbook used to keep track of purchasing. I have made drop down lists of current suppliers, brands and types. What I am trying to do is add a function that allows me to check how much I have spent, for example, on Microsoft products in a month (and in a year).

I have used 'Conditional Sum' which works to a point. I can specify my different 'conditions' and it adds up all matches fine.

The values for the drop down lists are all contained in a separate sheet within the same workbook. I have linked the values from the drop down list sheet to appear in each month's sheet - so that I can put the calculation next to each one.

The problem I am having is that I know that other people will be adding more and more suppliers/brands and types as time goes on.


This presents two problems:

1) I am not in a position to keep making new rules every time a new entry is added to each of these categories.

2) I am trying to add functionality that will allow me to sort them alphabetically - so that the drop down lists continue to make sense. However if this were to happen then the calculations created next to the lists would go out of whack whenever I did an alphabetical sort.

I have tried to alter the formula that Conditional Sum generates so that it looks at the cell to the left (cloned from the drop down menu list) for the value to search, so that the list could change as often as possible and the sum to just appear next to the value being searched for, but this does not work and just returns '0'.

Does anybody have any idea if there is a better way for me to achieve this?

Or indeed, does anybody still understand what I am trying to do?

Any help would be really appreciated!

Thanks

Sam

[NBVC]

Maybe a sample workbook attached showing what you have and what you need would help to understand better your needs.

[wanwan]

Thanks for the response.

I've attached the worksheet as it currently stands.

I've managed to get it working in a way where I can select a supplier from a drop down list (A34) and then get the resulting calculation (in B34).

What I am trying to achieve though, is to get the values to populate in M, from the value list in L (which would update when the lists in the 'DDL' sheet change..

Please let me know if any of this doesn't make sense..

Thanks!

[VBA Noob]

Wanwan,

Please read the forum rules below and then add the link to the other excel forum as cross posting without the link is not permitted

VBA Noob

[NBVC]

As a starter, the conditional Sum(IF()) formulas you are using are array formulas and so must be confirmed with CTRL+SHIFT+ENTER keys, not just ENTER.

You can do as you did and include a bunch of blanks in your list so that additions can be added or you can use a formula in Data Validation to force the list to show the items entered in the column and not need to include blanks... see here for instruction:

http://www.contextures.com/xlNames01.html#Dynamic

Sorting is another issue and may require VBA....

[wanwan]

Sorry VDA Noob, didn't realise.

Link:

http://www.mrexcel.com/forum/showthread.php?t=354730


NBVC

Thanks a lot for this.. it is very helpful and has done the trick.

I will read of about alphabetising now...

Very much appreciated!