# Dynamically generating a list based on values in another column

#### JonDalton

##### New Member
I'm somehow failing to Google my way out of this...

I have 2 named ranges, Items, and Users. As I have a lot of Items with Users of ""/0 I want to populate a side list containing only Items with Users >0.

My first Item starts on row 10 and I have created (stolen) the following array formula.

=IFERROR(INDEX(Items,SMALL(IF(Users<>"",ROW(Items)),ROW(10:10))-1,1),””)

However, I've had endless problems trying to modify this for my needs as array formulas are not my strong point.

Can anyone assist me with this, please?

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Hi,

My initial recommendation would be to test :

=IFERROR(INDEX(Items,SMALL(IF(Users<>"",ROW(Users)),ROW(10:10))-1,1),””)

HTH

I've dropped the named ranges for testing purposes. Your amended formula works if I remove the -1 and the cells begin in row 1, modifying ROW(1:1) to 10:10 seems to be breaking something (#NAME).

Thanks, I may have to just drop maintainability until I'm a little smarter.

Hi again,

Would recommend the following Array Formula...( if your data starts in Row 2 ...)

Replace rng ... with your own range ...

Code:
``=INDEX(rng,SMALL(IF(rng<>"",ROW(rng)-MIN(ROW(rng))+1),ROWS(\$2:2)))``

Hope this will help

Hi again,

Would recommend the following Array Formula...( if your data starts in Row 2 ...)

Replace rng ... with your own range ...

Code:
``=INDEX(rng,SMALL(IF(rng<>"",ROW(rng)-MIN(ROW(rng))+1),ROWS(\$2:2)))``

Hope this will help

Gosh that's fantastic, thank you so much!

Gosh that's fantastic, thank you so much!

Glad you like it ... :wink:

Thanks for your Thanks ...

Replies
13
Views
275
Replies
5
Views
256
Replies
3
Views
205
Replies
5
Views
155
Replies
3
Views
488

Threads
1,203,396
Messages
6,055,163
Members
444,767
Latest member
bryandaniel5

### 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

### 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