Indirect function for continuously changing range

kiwiDD

New Member
Joined
Jan 25, 2012
Messages
6
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!
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Jan 25, 2012
Messages
6
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top