I have always found it to be such a pain to add pulls to the library, with all of the different steps that it takes, and places you have to add things.
One of the things that I find to be a pain is that once you add your new pull to the LookUpTablePullType, you then still have to go to the Global List_Pull_Types_Inch or List_Pull_Types_Metric and add that pull to that list, exactly as you formatted the name in the LookUpTable, before you can select your new pull in the Project Wizard or in your product.
Well, I think I have come up with a solution to eliminate at least that step.
Using a combination of some different functions, you can create this list of pulls by directly pulling in the whole first column of the LookUpTable. So as soon as you add a row to the LookUpTable, it will add it to the list automatically.
The formula I used is:
=rangetostring(OFFSET(LookUpTablePullType,0,0,ROWS(LookUpTablePullType),1),"|")
So using the rangetostring() function, we can pull in a range of values, and concatenate them into a string, and delimit every value by "|".
Since we already have a defined range in the LookUpTable, I just needed a way to limit that range to the first column. We do this using OFFSET, which allows you to modify a range down to a defined number of row and columns. We know we only need 1 column, but it is the Rows that needs to be dynamic. Using the ROWS() function we can count how many rows are in the LookUpTablePullType range.
One thing I did, was I added a row at the top of the LookUpTable with "None" in the first and second cell, that way we still have the option to pick "None" for pull type from the drop down list. This also is nice, in that instead of leaving errors in your hardware names, it brings in "None", which plays nice with things like your Aventos Flip Up door hardware.
Since Pulls with a length in the name are normally added with a formula like this:
="Wire Straight"&" "&IF(Pull_Metric_Names=0,"3 Inch","75mm")
the same dynamic list can be used for the G!List_Pull_Types_Metric, since the names are converted in the look up table. Really we would only need one Global List in that case.
However, if you wanted to have two separate lists, you could easily just add a column at the end of the LookUpTable where you could have the Metric Name written statically, and reference that into the G!List_Pull_Types_Metric.
To me, this setup could be used for any global list pulled from a LookUpTable. I have since added a version of it to G!List_Drawer_Slides:
=rangetostring(OFFSET(LookUpTableSystemDrawerSlide,0,0,ROWS(LookUpTableSystemDrawerSlide),1),"|")
If you found this tip to be helpful please like this post and share! Check out my other Microvellum tips by clicking the topic links below, and if you have any questions please send me a message by clicking on the contact page up top.