krislopmar
New Member
Hello, I have the following set of data:
1 AGENCIES
2 German Agency 1
3 German Agency 2
4 German Agency 3
..
10 Total Germany
11 Swiss Agency 1
12 Swiss Agency 2
...
15 Total Switzerland
ETC
In order to create a list in another sheet that allows me to choose any from the German agencies I have created a dynamic range $A$2:$A10, but I have to add another agency (for example between number 2 and 3) the dynamic range doesnt become $A$2:$A11.
The problem is that there is a long list of agencies with subtotals per country and for each one of the countries I need a different range name set up. The only way I modify this long list is adding agencies by inserting rows where it corresponds. I dont know how to fix this. Any help will be welcome and very much appreciated!! Im using Excel 2007. Many thanks!
1 AGENCIES
2 German Agency 1
3 German Agency 2
4 German Agency 3
..
10 Total Germany
11 Swiss Agency 1
12 Swiss Agency 2
...
15 Total Switzerland
ETC
In order to create a list in another sheet that allows me to choose any from the German agencies I have created a dynamic range $A$2:$A10, but I have to add another agency (for example between number 2 and 3) the dynamic range doesnt become $A$2:$A11.
The problem is that there is a long list of agencies with subtotals per country and for each one of the countries I need a different range name set up. The only way I modify this long list is adding agencies by inserting rows where it corresponds. I dont know how to fix this. Any help will be welcome and very much appreciated!! Im using Excel 2007. Many thanks!