Indirect function for continuously changing range

[kiwiDD]

Hi. I have this little program where you can add items to an index (it's do with foods). So you select the food category the food belongs to then the macro will put it in the respective place in the index (under the correct category heading and in alphabetical order). I then draw from this index on another sheet using drop down boxes. The first drop down box will name the category, the second box you can choose the food type. If the index were to stay fixed then there would be no problem using the indirect function as the range of cells will be of fixed size, however the range of cells is constantly changing. I have noticed that if you insert rows in between other rows in a named range then the name range will expand automatically. However if you add a row onto the end of the range, it will be outside the name range. Any ideas (layman) terms how to tackle this problem? Thanks!

 

[T. Valko]

Hi. I have this little program where you can add items to an index (it's do with foods). So you select the food category the food belongs to then the macro will put it in the respective place in the index (under the correct category heading and in alphabetical order). I then draw from this index on another sheet using drop down boxes. The first drop down box will name the category, the second box you can choose the food type. If the index were to stay fixed then there would be no problem using the indirect function as the range of cells will be of fixed size, however the range of cells is constantly changing. I have noticed that if you insert rows in between other rows in a named range then the name range will expand automatically. However if you add a row onto the end of the range, it will be outside the name range. Any ideas (layman) terms how to tackle this problem? Thanks!

You can use a dynamic range.

Sounds like you're dealing with TEXT. Here's an example of a dynamic range that contains only TEXT type data.

=$A$2:INDEX($A$2:$A$100,MATCH("zzzzz",$A$2:$A$100))

In this application the range is defined starting from cell A2 with a maximum ending point at A100. You would use an end of range that allows you enough cells for future data addition.

 

[kiwiDD]

So say you have data in cells A2-A50, do you have leave blank rows in A51-A100? Because when one category finishes there is only one row spacer and then the next category starts.

 

[T. Valko]

So say you have data in cells A2-A50, do you have leave blank rows in A51-A100? Because when one category finishes there is only one row spacer and then the next category starts.

Well, that changes things!

Can you post some sample data and give more details as to what you're wanting to do?