Solved: add data based upon created categories

[joeyc]

Please scroll down and see revision. Disregard these comments.


BACKGROUND::
I have attached a file. It makes what I will describe easier to understand.

In A2:A21 I have sectors of which a bond can fall into. Database #1 wants to know the percentage of Corporates, Gov't and Other. This database considers all the yellow items to be Corporates, all the light green items to be Gov't and all the tan items to be Other. The answers to these are in D2:D4.

The problem is, the list in A2:A21 is long and I don't want to have to go through this list over and over to classify items. If I could do the example below, I can solve my problems.


MY GOAL::
I would like an user to be able to select up to 5 sectors [B26:B30] and enter a percent in the adjacent cell. Next, Corporates, Gov't, and Other should be calculated in E27:E29 based upon the criteria in B26:C30.

[Ken Puls]

I'm curious if you are set on VBA for this. With the addition of a column to actually record the classification, I think you can do everything you need with formulas. This has the advantage of being a bit more intuitive for the users, as well as faster.

Now, having said that, I probably butchered your logic a bit, and Bob Phillips is going to wince when he sees my lack of sumproduct, but have a look at the attached.

Hope it helps,

[joeyc]

Thank you for your efforts. I don't think this quite does it though. Give me a few minutes and I will post a revised version of my work.

[joeyc]

I will try to describe this once again. I don't think my initial posting was very good.

1.) In A1:D21, I have a chart that categorizes sectors in three different databases. You will notice, Sectors are classified as different categories for each database. Row 3 [which is in blue] is a good example.
2.) In G1:H21, I am provided with data.
3.) I need to enter this data into A25:B29. [I only have 5 places to enter data. This is enough.]
4.) After, I enter the data here, numbers should replace the ??? marks in Database #1, Database #2, and Database #3.

** In a nutshell, I am trying to take a predetermined set of classifications [this is the chart in A1:D21] and generate numbers for Database #1, Database #2, and Database #3 based upon criteria entered into A25:B29. **

I am definitely open to using formulas. But, :dunno

[Ken Puls]

Sorry Joey,

I've been busy for a few days, and don't have time to look at this tonight either. I'll try to get back to this tomorrow, unless someone else picks it up.

Cheers,

[Ken Puls]

Hi Joey,

Help me out with this a bit more...

Can you fill in the expected values by hand for the other sections? For example, if you choose certain sectors in A25:A29, (I don't care which,) can you fill in the values you'd expect to see in B25:B29, as well as the value for columns E, G, I, rows 25:27?

I'm thinking it would be easier to see with a completed case, as I still feel I'm guessing a bit.

[joeyc]

Due to contractual obligations, I am showing you a modified version of what is troubling me.

I am concerned with A110:B138. Data for bond sectors is entered here. The total should equal 100%. After, it is grouped. You can view Database#1 B40:B51 & Database #2 E20:E35 as examples. If you look at my cell references, you can see how I want the stuff grouped.

The problem? To list 27 different sectors on a worksheet is sloppy. In reality, when I see a product, never are more than 5 sectors used. So, what I would like to do is have 5 drop down menus where one can select a sector. Then, he enters the percent it makes up. Finally, it gets linked over to database #1 and database #2.

Maybe a vlookup would do it? What do you suggest? How can this be approached?

[Ken Puls]

One issue that you had is that you didn't really set up a database in truth. You had a list, but it wasn't related to anything.

What I've done here is to add a new sheet for Tables, and created you a database of your Asset Types, and what categories you want linked for your Database1 and Database2 reports. The final column has a summary (using SumIF) of the amounts by proportion.

The DB pages then use SumIf functions of their own to allocated the proportions appropriately.

I also adjusted the first page to use the dropdown data validation lists as you were looking for.

HTH,

[joeyc]

I saw how you did it. It works great. I can hide the worksheet so that won't be an issue. Nevertheless, the formulas you written make a lot of sense and my Input sheet won't be clogged up with so much data now. Thank you so much.

[Ken Puls]

Glad to help, Joey.

One thing I noticed, though, was that you need to force your users to have 100% chose overall for your choices. This is a place where VBA could play a key role for you.

It's easy to set up a data validation rule to ensure that the sum of your data range isn't greater than 100% to catch people going too high. The challenge, of course, is that you can't invoke a rule to warn people with less, as they may still be inputting values.

VBA could help you here, as you could run a validation routine to check if the specific range equals 100, and trigger it at a specific time. Some examples might include:
-When they go to leave the sheet (through the Worksheet_Deactivate event)
-When they activate a cell range outside of your 5 input cells (through the Worksheet_SelectionChange event)

You could then return them to the range to review it. ;)

[joeyc]

Your suggestions make a lot of sense. It is possible that someone can mess up and not have the total equal to 100%. But, this hasn't been a problem in the past. So, I would like to focus on other things. Thank you for your efforts.