Why do I use dynamic named ranges?
Where I work, most reports are exposed via a web front-end and Excel can create an external connection and retrieve the information. This is much safer than using direct database connections in workbooks. A problem with web queries though is that they cannot be converted to Tables in order for referencing columns and the dataset as a whole to be made easier. As a result, dynamic named ranges are a necessity for producing easy to develop and manage spreadsheets since the volumes in the raw data can change over time.
How I save myself time
A raw data table with 20 columns will take a long time to create the named ranges for, given that I want:
- A dynamic range covering the headers too for pivot tables
- A dynamic range without headers for vlookups
- A dynamic range for each column without headers
I use a macro, assigned to a nice button on my ribbon, to generate all the relevant ranges.
What are the special considerations?
Structure – raw data tables should ALWAYS be set up in a specific way – with the Primary Key on the left hand side and always filled in, with no empty rows or columns
Special characters – range names can’t contain special characters. The VBA uses the RegEx functionality to strip these out.
Numbers – range names can’t have numbers either. We can’t just strip out the numbers like we would special characters because they might be important like Grade1, Grade2 and Grade3 and collapsing them all to the name Grade would be a problem. Instead, the macro converts all numbers to the corresponding letter in the alphabet.
How much the data will grow? By default I set the macro to use 10 times the number of records present when I run the macro – if it’s already bigger than 25k rows, the number will need to be reduced, and if I don’t think 10 times the number will be adequate, I’ll increase the number.
» Read more