Drop down lists cross sheets with more than one column.

**jxales** · 07-09-2013, 01:53 PM

Hey guys,

Im trying to make my life easier seeing as my boss wants me to start making orders for materials at work.
The current way he does things just takes too long and writing down all kinds of crap is just a waste of time imo.
So i figured id make an excel drop down list for the materials however this has been a lot more difficult that i thought.
Was hoping that someone could help me out on a problems im having.

So i googled my way to finding out how to do lists, however i cant seem to find a solution to having more than one column of info show per selection.

Ill explain here what im trying to do and have attached an example of it as well.

So:
On sheet1 (Materials) i have a list of materials that i need to do a certain job.
On sheet2 (Order) i want to be able to have drop down lists that takes information from sheet1.
On sheet3 (Lists) i have my lists.

So on sheet1 i note i down the amount of X materials that i will need to do a certain job.

I want that on sheet2 (order) that i can select for example black paint from the drop down menu. and it inserts the amount from sheet1 and also inserts Buckets or packets for nails.

So if i select Primer from the drop down i would get "Primer 15 Buckets"

After i select an item it would automatically insert a new row with another copy of the drop down menu that was above it so i could also select black paint and get "Black Paint 15 Buckets"

And i also have a remove option if i want to remove that selection..

Now is this even possible?
ive been googling for an hour and havent had the best of luck :/

**JBeaucaire** · 07-09-2013, 09:56 PM

Put this formula in D5, then copy down and across as needed:

=IF(LEN($B5)<4,"",INDEX(Material!C:C,MATCH($B5,Material!$A:$A,0)))

**jxales** · 07-13-2013, 08:57 AM

that works, what does the number 4 mean in the formula to return ""

How would i make selecting something from the drop down list create a new row with the same dropdown list in it?

And when i select remove the return is #N/A. Is there not a way to delete that row if remove is selected?

**JBeaucaire** · 07-20-2013, 12:52 PM

Originally Posted by jxales

that works, what does the number 4 mean in the formula to return

1) You have a " - " entry in your drop down, most people don't do that. Just pressing DELETE on the cell blanks it out. But since you have that 3-character string IN the list, I'm testing for choices that are less than 4 characters long and doing nothing if that is the case.

How would i make selecting something from the drop down list create a new row with the same dropdown list in it?

2) Formulas do not make structural changes to worksheets, they simply change the displayed results in that cell and any other cells that refer to them. Making structural changes to a worksheet would require VBA programming.

And when i select remove the return is #N/A. Is there not a way to delete that row if remove is selected?

3) Physically delete the row, no, that would require VBA programming. But like 1) above, the formula could be changed to do something different if REMOVE is the selection. What would you like the display to be if REMOVE is chosen?

**jxales** · 07-24-2013, 01:36 PM

Ah thanks for the clear up. I've gotten some help with VBA to make buttons that insert new rows and a button to delete active row.
That will have to work for now.

Is there any specific reason why MS hasn't made functions for formulas that can do structural changes? wouldn't that make things easier for the end user?

Also that buttons can't be in cells and need referencing etc. strange

**JBeaucaire** · 07-24-2013, 01:52 PM

The way Excel is designed is pretty logical and straightforward. Formulas are for displaying calculated differences. VBA is for pretty much everything else, for people with more exotic needs than just getting answers. Like youreself. It is what is it, not strange at all.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.