Dynamic Named Range to Auto-Populate another range based on criteria

Hello,

I'm sure I've seen this done but I can't remember in what context it was. I'm stuck. Please take a look at my attachment.

In column A I have a dynamic named range using the formula

Formula:

Please Login or Register  to view this content.

.

In column B, there is a status indicator of Yes or No.

In column D, it shows how the output I need should look. If it can be output alphabetically, great, but not a problem if they can't.

Column F is where the list would be using the formulas necessary to populate the list with only the names that have an Active status of "Yes". If any of them change from status of Yes to No, they should automatically remove from the list in column F.

Can this be done with formulas or would it have to be done with VBA?

Thanks for any help or suggestions you can offer with this.

Sounds like you want something like this:

Lookup with multiple instance of the lookup value

Hey Tony,

That's exactly what I was looking for. I knew I had seen this before but just couldn't remember where or for what exactly.

This forum is awesome.

Thanks very much!

You're welcome. Thanks for the feedback!

Hi Tony,

Sorry to bug you on this since the formula you provided works perfectly in my test workbook but, I'm having a difficult time trying to get it to work in my production workbook and I was wondering if you might know already why it has the problem...

In my production workbook, a formula like what you provided is on a different sheet than where the desired data is being pulled from and I'm trying to use the named ranges on that other sheet in the formula.

For example, my formula looks like this:

Formula:

Please Login or Register  to view this content.

The problem is that nothing is getting returned when using the Youth_Graduated named range but if I put the number 1 into a column outside of the table for each record where Youth_Graduated is located, the correct data is displayed.

??? It seems like it has something to do with the fact that the numbers in the Youth_Graduated column are themselves derived from a formula such as

Formula:

Please Login or Register  to view this content.

. F14 is on the same sheet as Youth_Participants and Youth_Graduated and refers to a date field that will be either a dash "-", blank or contain a date.

I updated my example file to mimick what I'm doing in my production workbook. It shows three names in the green area and one of those three doesn't even match the criteria... There should be 9 names populating that do match. If I use the same numbers in another column that those numbers have all been entered into by hand, there's no problem. Strange....

Thanks again!

Try this...

=IFERROR(INDEX(Youth_Participants,SMALL(IF(Youth_Graduated=1,ROW(Youth_Graduated)),ROWS(A$2:A2))-MIN(ROW(Youth_Graduated))+1),"")

Still array entered!

Yep, that was it. Thanks again, Tony. I guess its time to read up on SMALL, ROW, ROWS and MAX and MIN....

You rock, dude!

Good deal. Thanks for the feedback!