Cascading lists, Indirect and Dynamic ranges VBA Macro Code

Hi Gurus...

I'll start by introducing myself as a newbie to the site and look forward to engaging regularly as my excel enthusiasm continuously grows by the day. I'm technically minded but my career digressed from IT programming over a decade ago, dinosaur 1st Gen language. I'm trying to develop a smart spreadsheet solution to aid my job.

I have attached a test spreadsheet I created and I would deeply appreciate your assistance on. I created a cascading list spreadsheet which allows the user to select a category from a drop down list in A2 of Sheet 1 and depending on the category selected, the next cell B2 beneath the title sub-category uses the Indirect function to display the relevant list for the selected category A2. These Category and and subcategory details and ranges are stored on Sheet 2. The sub-category list ranges are named after their respective category titles on the top row. Simple so far.... The Sheet 2 details could be inverted, as in the far left column down to hold the category titles and their respective sub-category detail ranges to be listed across...

Obstacles: Indirect function does not work with dynamic named ranges therefore I cannot find a way to automate new category or sub-category details to the lists and the ranges to automatically adjust accordingly. Unless it is possible for code to to expand a range somehow by inserting a line between the range as is possible manually and inserting new text? Or is there a way for code to carry out an offset function and expand a range when executed?

My Question and background: On Sheet 2 on the very top I have created a button titled add new category and sub-category. What I would like is help on the code to attach to this button so that when clicked it will do the following...
Show the user a form to enter a Category & Sub-category and hit a create button.(This I can do myself). The code function I need is for excel to then search the existing category titles top row-across (or left row-down if inverted) for a match with the new category entered in the category form field. If category match is found then search its respective subcategory range for a match with the newly entered sub-category (if provided) and if no match is found on that, then create a new sub-category under that existing category at the end of list and re-adjusting range to incorporate this new sub-category. If there is also a subcategory match then return and error that both already exist. If there is no category match from the titles row, then add a new category at the next free column of the top row (or row in left column if inverted) and add the new sub-category (if provided) under it and create a new range name for it.

https://www.dropbox.com/s/1vpx3vyeo9...n%29.xlsx?dl=0


If this can be done I would be unbelievably grateful as I have been pulling out my hair out with this....

Thanks in advance

Thanks very much for the very useful link. took me a while to understand it but once the penny dropped I was off with the wind!!