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:
Code:
=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:
Code:
=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:
- 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
- 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".
- 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.