Grouped items / Data Validation / Userform / Drop Down / VBA

[thedefense]

Hey guys,

I've been trying to figure out a way how to be able to have a drop down menu that consists of "grouped items" i.e. items that fall under the same group/category; and once a group item/category has been selected, the individual items need to be automatically copied down on separate rows.

I've attached my excel file consisting of two tabs: an item list and an estimate template. On the item list, you can see that column B designated as "Group Name" consists of several individual item names and also a "subtotal" item that needs to calculate the total price for that particular group name. My goal is to be able to formulate a way (either by a drop down list, data validation, or userform) to be able to select "Group Name" under the "Estimate" tab and then have all of its individual items as well as the subtotal pasted one after the other. I would also consider using check boxes to select group names.

Any help or guidance on this would be highly appreciated,

Thank you.

Example.xlsx

[ajryan88]

Hi,

Please take a look on the "Demonstration" worksheet in the attached workbook.

Is this the sort of thing that you were looking for?

Let me know

[thedefense]

Thank you for your help! That works perfect! Just one question, would it be possible to have the same drop down menu copied down the entire column F (under the tab "demonstration") so that if I selected the group "Main Office" it would show the individual items of that group + its subtotal; but then if I selected "Library" after that, I would have all the individual items of Library shown. In essence, the estimate contains more than more grouped items at the same time. I've tried tinkering with the formula but I don't want to mess it up! Also, how would I go about embedding this formula to a Userform dialogue? Thanks for all the help.

[ajryan88]

No problems.

It is possible, but only if you will have a fixed number of items in each group. I'm sure there would be a very very long formula that could handle it, but I'm going to do my best to talk you out of asking me to write one, as it would take me quite a while to develop one, and I couldn't even guarantee that it would be perfect

As for incorporating this into a UserForm, you wouldn't need the formulae. You could simply use a series of For...Next and/or If...Then loops to achieve the desired result. How would you be looking at displaying the data on the UserForm? In a ListView control? Let me notch up a ListView for you and show you what I mean...I'll be back in a little bit

[ajryan88]

OK, it's pretty rough at the moment, but hopefully you get the idea.

Let me know

[thedefense]

Hello Ajryan,

That's more than enough to get me started. Thank you so much for all the help.

Cheers

[ajryan88]

You're welcome

Please don't forget to mark this thread as solved

[Testie]

Hi. I have this very same question but I don't know where the attached workbook with demonstration tab is. I can only see the example.xlsx

[NANDOM]

Hola a todos,
Soy nueva en el grupo y no encuentro la modificación que hicieron para que el trabajo quede resuelto.
Podrán indicar en donde se encuentra el archivo corregido?
Gracias!!

[Leith Ross]

Hola Nandom,

ˇBienvenido al foro!
No veo el archivo corregido. Le pediré a ajryan88 que vuelva a publicar el archivo.

[Leith Ross]

Hello ajryan88,

I do not see the file you posted earlier. Please repost the workbook so everyone can follow what has happened.

[AliGW]

That was in 2014, so a heck of a lot earlier! It’s been a long day ...