Dynamic Drop Downs with Large Table

[Jon childs]

Hello,

I have a sheet named Material. On this sheet is a very large table called mattbl. The table contains thousands of products. I organized the table by Item Code, Divisions and Subdivisions, etc.

I have another sheet called Takeoff. On this sheet is a table called Takeoff. I enter a Item Code number in the cell under the column Item and the cells to the right use VLOOKUPS to get the price, labor etc.

This has worked very well however the data has grown very large and takes some time to try and find the right Item code. I thought using a series of Dynamic Drop Downs would help speed up the process. Instead of typing in the Item code, I would choose a division (PVC, EMT, Boxes) then a subdivision (PVC Schedule 40, etc) then choose the Item (1/2" PVC Schedule 40). I have spent several days studying Dynamic Dropdowns and follow most of it, but I just cant seem to get the last dropdown box to work. When I try using index it brings the spreadsheet to a crawl because there is so much data, but maybe I am typing it wrong. I have attached the spreadsheet.

 

[jeffreyweir]

Jon - I think a potentially better approach here would be to replace most of the Takeoff table with a PivotTable, which would do away with a lot of those VLOOKUPs in the first place.

Are you familiar with PivotTables? Any reason you can't go down this path?

 

[Jon childs]

I havent thought of using PivotTables for this. My understanding and use of PivotTables has been primarily used as a reporting function. How would a pivottable replace the VLOOKUPS?

 

[jeffreyweir]

If you make a PivotTable out of mattble, then we can use the GETPIVOTDATA function to directly reference the required information from the PivotTable rather than doing lots of VLOOPUPS over a very large mattble range.

If that doesn't work or doesn't make things any better, we can also replace your VLOOKUPS with and INDEX/MATCH combo which is more robust and faster to boot.

But first, in terms of that 3rd dropdown - the Item dropdown - you mentioned you'd tried something using INDEX. What exactly did you try?

Also, for cascading lists (aka dynamic cascading dropdowns) with more than two levels, the INDIRECT approach you are using can get clunky. Here's a couple of posts I did at Chandoo.org a while back that set out an altenate approach that may suit this much better:

http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/

http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

Download the sample workbooks and have a look. I can possibly help you set this up for your file. Better than using INDIRECT and named ranges, because you don't have to maintain those named ranges should anything change (e.g. a new category, or products getting deleted)

 

[Jon childs]

You have given me some great info here, thank you.

I had already read your article and downloaded the sample file. My Sheet Data was my attempt of trying to recreate your sample file but were I got stuck was my column ItemDescription.

I am going to first start with dynamic cascading drop downs. I created the MainList range and I used the formula;

=INDEX(MatTbl[[Division]],1):INDEX(MatTbl[[Division]],COUNTA(Mattbl[[Division]]))

This gave me a really long list with all the duplicates and blanks. So on the data sheet I created a quick pivottable and got the Division labels without duplicates or blanks. I then copied and pasted them vertically and created a named range called Division. I am sure thats not the right way and I am going to try and figure out how to get the blanks and duplicates out of MainList.

 

[jeffreyweir]

You're on the right track...you've just turned off too early!

Put the word "Choose..." into cell D2 of the 'Data' sheet to the left of the Aluminium column and paste those 'Division' labels below it. Then select D2 and turn the whole block into an Excel Table by pressing the Ctrl + T keyboard shortcut. (Make sure the "My Table has headers" box is ticked).

Rename that new Table on the Data sheet 'Table1' so that you can reuse my formulas at that blogpost without having to edit them.

Now add the Mainlist formula in the Name Manager:

=INDEX(Table1[[Choose…]],1):INDEX(Table1[[Choose…]],COUNTA(Table1[[Choose…]]))

IMPORTANT: Now select cell B1 in the Takeoff Sheet.

Then enter the SubList formula in the Name Manager from the blogpost:

=IF(OR(A1="Choose…",A1=""),"",INDEX(Table1,1,MATCH(A1,Table1[#Headers],0)):INDEX(
Table1,COUNTA(INDEX(Table1,,MATCH(A1,Table1[#Headers],0))),MATCH(A1,Table1[#Headers],0)))

The reason you have to select B1 that the SubList formula above has a relative reference in it to A1. Selecting B1 when you add this formula means that Excel will think:

"Aha, I get it...the cell you've selected when entering this formula into the Name Manager is B1- and the formula you're entering into the name manage has a relative reference in it to A1, which is one cell to the left.
So whenever I use this formula, I should always replace that relative A1 reference with the address of whatever cell is immediately to the left. Okay, I can do that for you ".

So that means that now your first two levels of dropdowns are in place. But that 3rd level is problematic on two counts:

Problem One: your 2nd level category list isn't unique. What I mean by that is that these ItemDescriptions are found in more than one SubDivision:

[code = text]1" Ground Clamp, Bare Wire 2
1" U-bolt, Nut 2
1/2" ARC - Clamp Mount 10
1/2" ARC - SS Strut Mount 10
1/2" ARC - Strap Mount 10
1/2" ARC - Strut Mount 10
12x12 Panel 2
1-5/8" Single Channel, PVC Coated 2
15a 125v Duplex Receptacle 2
15a 125v Duplex Receptacle, TVSS 2
1-Gang Non-Metallic Device Box 2-3/4" 2
2" Ground Clamp, Bare Wire 2
20a 125v Duplex Receptacle, TVSS 2
2-1/2" 1-Hole Steel Strap 2
2-Gang Non-Metallic Device Box 2-7/8" 2
3" 1-Hole Steel Strap 2
30a 240v Safety Switch, Double-Throw, Nema 1 2
3-1/2" 1-Hole Steel Strap 2
4" 1-Hole Steel Strap 2
4x2-1/8" Round Non-Metallic Box, Ground 2
5/16" x 4" Lag Bolt 2
6" Ground Clamp, Bare Wire 2
Cable Organizer Panel 2
Cable Organizer Tray 2
Cable Support Bracket 2
Cable, Category-5, Backbone, 25-Pair 2
Cable, Category-5, Backbone, 50-Pair 2
Floor Mounted Rack 2
Floor Rack with Vertical Cable Manager 2
Hinged Wall Mounted Bracket 2
Swing Out Rack 2
Vertical Cable Manager 2
Wall Mounted Rack 2[/code.

So in order for my approach to work you will have to rename these so that they are unique in order to get dropdowns to work. e.g. for 1" Ground Clamp, Bare Wire you would have to further identify it like so:

• 1" Ground Clamp, Bare Wire - SomeCategory
  
• 1" Ground Clamp, Bare Wire - SomeOtherCategory

Problem Two: You have 593 different 2nd level categories. If you look again at the blog post I refered you to, you'll see that you need to add a new heading for each and every one of them to the right of that table, similar as you did with the 1st level categories - and then list the choices that apply for each of them below the relevent heading.

So for instance,

• there would be a column called ARC - Clamp Mount and listed under that column header would be just one entry: 1/2" ARC - Clamp Mount because only one choice is relevant to that category.
• And there would be a column called 1 5/8 Unistrut and listed under that column heading would be a whole bunch of things:
  1 5/8 Unistrut, 1 5/8 Unistrut 316 Ss, 1 5/8 Unistrut Fiberglass, 1 5/8 Unistrut P1000-20Gr, 1 5/8 Unistrut P1000-20Pg, 1 5/8 Unistrut P1000Hs-20Gr, 1 5/8 Unistrut P1000Hs-20Pg, 1 5/8 Unistrut Pvc/Grc

This would be time consuming to say the least. And getting those lists would require lots of cutting and pasting. That said, there is a nifty way that you could get Excel to produce them for you, using something called the Show Report Filter Pages option in Excel. Try this...it will blow your mind:

1. Make a new copy of one of the Pivots you've already made, and with DivisionDescription in the Filters pane, and put SubDivisionDescription in the Filters pane and ItemDescription in the Rows pane
2. From the Analyze contextual tab that comes up when you select the Pivot, click the little arrow to the right of Options that shows at the far left of the ribbon, and select "Show Report Filter Pages".
3. A dialog box will come up saying Show all report filter pages of: and it will have the word DivisionDescription highlighted. Click OK. EXcel will then create a new PivotTable on a new sheet for every one of those 593 Level Two categories. You can then cut and paste each of these into the dropdowns table.

In fact, I could code up a macro to do this for you, but given you still have the non-unique problem mentioned above, I think a whole new way of doing this would be better, and I've got an idea forming in my mind, where we could do some clever lookups on a PivotTable using something called Range Slicing to just pull the lists required.


I've got quite a bit on at the moment so I'm not sure when I'd get around to this.
but this is something I've been meaning to tackle in any case.

 

[jeffreyweir]

Jon - I've found a relatively simple way to implement this. Give the above a read, because it's worth learning this stuff. And try the PivotTable trick I mention, because that's a great one to know. But don't bother implementing the above. I'll post something back here sometime soonish with something mindboggling.

 

[jeffreyweir]

So I found a way to implement the three way dropdowns, but I can't post the file because now it's too big. If you email me on weir.jeff@gmail.com I'll email it back to you.

Note that you still have to do the lookups, and as implemented your VLOOKUP may not return the correct result for these items:

• 2-1/2" 1-Hole Steel Strap
• 3" 1-Hole Steel Strap
• 3-1/2" 1-Hole Steel Strap
• 4" 1-Hole Steel Strap

...because they appear in both the EMT and GRC categories. i.e. they are not unique.

Regardless, you could actually implement a similar approach to what I've used to do the cascading dropdowns to look up the correct item. In fact I would have done this for you but I can't see how some of the headings in the Takeoff sheet match those in the Material sheet.

 

[jeffreyweir]

For anyone following along at home, here's a file containing just the data validation approach I take, using something called Range Slicing. This can be used to do away with SUMPRODUCT formulas as well.

I don't have time to explain it here, so you'll have to poke around in the Name Manager to see what's going on. But I will write up the approach and put it on one of the blogs sometime in the near future. In fact, this is going in my book.

 

[Jon childs]

I just sent you an email... Thanks again for all the help. I also researched the GETPIVOTDATA and its awesome. I changed the vlookups to using the GETPIVOTDATA and it works great. I am going to go thru your file you uploaded and follow your steps.

 

[jeffreyweir]

Hi Jon. I'll need to explain some tricky stuff with regards to those formulas referenced by the lookup named ranges...they can be devillishly tricky to implement. I plan to write up a blog post sometimes fairly soon on this, so will try to remember to post a link back here to it.