Dynamically generating a list based on values in another column

[JonDalton]

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?

 

[James006]

Hi,

My initial recommendation would be to test :

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

HTH

 

[JonDalton]

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.

 

[James006]

Hi again,

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

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

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

Hope this will help

 

[JonDalton]

Hi again,

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

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

=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!

 

[James006]

Gosh that's fantastic, thank you so much!

Glad you like it ... :wink:

Thanks for your Thanks ...