Using a drop-list to categorize and insert cells

[Daemonion]

Please see the attached file as I feel it explains it the best.

Essentially, I am creating a template were users can categorize items they input with a drop-list.

STEP 1: User lists their items that need to be categorized in F4, F5, F6 ...and so on.

STEP2: The user then uses a drop list in H4 to "categorize" the item in F4. And the drop list in H5 to categorize the item in F5, etc.

STEP 3: Excel reads what the item in F4 has been categorized as in H4, and adds it under the appropriate cell in the template located in Column A.


EXAMPLE

STEP 1: The user adds "advertising", "bad debt" and "electric" to cells F4, F5 and F6, respectively.

STEP 2: The user clicks the drop-list in H4 and selects "Selling". This effectively tags "advertising" as type "Selling". He tags bad debt in F5 as selling in H5, and electric in F6 as Special 1 in H6.

STEP 3: Excel adds the data automatically so it looks like my attached example.


I also posted this question at OZGrid and VBAExpress, but have not gotten any replies.

[hemesh]

Hello and welcome to the forum ! find the attached It is with slight change !
Hope this helps
'
if this is helpful click "*" add reputation icon in the bottom left corner of my post!

[alansidman]

I'm not entirely sure I understand your situation. But having said that, maybe dependent combo boxes might work for you.

See this: http://www.contextures.com/exceluser...xes.html#intro

[Daemonion]

I'm not entirely sure I understand your situation. But having said that, maybe dependent combo boxes might work for you.

See this: http://www.contextures.com/exceluser...xes.html#intro

Thank you for your reply. I've never heard of dependent combo boxes before - that looks like an extremely powerful tool and I may be able to use it. Let me try to clarify my situation a bit more.

I am going to have lots of lists of revenue and expense line items pasted into excel like this.

I then want to be able to categorize each of those revenue and expense line items using drop-down menus, as shown in the sheet I attached. Once an item is categorized using a drop-down list, I want it to then appear into another sheet under the correct category. It should look like this.

Does that make sense?

[MarvinP]

Hi Daemonion and welcome to the forum,

I thought I'd give you some VERY fancy stuff to consider in answering your problem. Excel has things called Dynamic Named Ranges that can expand depending on how many rows are in the data. You can use these DNR's instead of ranges (like A2:B47) in formulas. You can even base Pivot Tables off these DNR names. To see the DNRs you need to look at the Names Manager that is in the Formulas Tab.

I also did some fancy stuff using VBA. It is called EVENT Code that will trigger whenever stuff changes on the sheet. You need to look at the VBA behind the individual sheet to see the Event Code. Then I created a macro that Refreshed the Pivot Table.

So here is what happens. When you add anything to columns E,F or G, the range I've called "PivotDNR" gets bigger and it refreshes the Pivot Table in Col A. Keep studying and it will make sense, but it isn't an obvious progression from simple Excel formulas.

[Daemonion]

MarvinP, that is exactly along the lines of what I am looking for. I will study this thoroughly and report back if I have any other questions. Thank you so much.

[MarvinP]

Hey Daemonion,

If you put Validation or not, my sheet should work. It looked like you knew how to do validation but there is a new trick for you to use. Instead of having a validation list that refers to a range like "K1:k10", you can now use the DNR NAME of the range in the validation list range input box. As you add more words to your list the list will grow without needing to change anything. Read up on DNRs.

http://www.bettersolutions.com/excel...G820716330.htm or
http://www.vertex42.com/ExcelArticle...ed-ranges.html