Drop down box using data from dynamic lists

[guerrilla_gorrila]

Hi all, I was hoping you could help me with a problem.

I have two columns, A and B, where column A denotes a unique identifier for a report and column B denotes the status the report is at (writing, issued, QA, archived, terminated). These are dynamic lists, where the user can insert a new report number to the next available row and select the appropriate status at any time. I have a dynamic list name of "StudyCode" for the unique identifier (Col A), and "StudyStatus" for the report status (Col B).

On a separate sheet, I would like to include a drop down box that lists only the reports that have NOT been archived OR terminated.

Does anyone know if this is possible and if so how it could be done?

Best regards,
Adam

[Tony Valko]

Does anyone know if this is possible and if so how it could be done?

You would extract all the identifiers that meet the condition into a new list then use this new list as the source for the drop down.

Post a SMALL sample file and I'll show you how to do it. A SMALL sample file will have about 20 rows worth of data. Remember, it's a SAMPLE file!

[guerrilla_gorrila]

Thanks! I have attached a sample file, I hope that's okay.

[Tony Valko]

On sheet Active Studies...

A1 = column header = Study Code

Enter this array formula** in A2:

=IFERROR(INDEX('Total Studies'!A:A,SMALL(IF(ISNA(MATCH('Total Studies'!B$8:B$19,{"Terminated","Archived"},0)),ROW('Total Studies'!B$8:B$19)),ROWS(A$2:A2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Then, to use this new list as the source for a drop down list...

As the source use this formula:

=OFFSET('Active Studies'!$A$2,,,COUNTIF('Active Studies'!$A$2:$A$100,"?*"))

Adjust for a reasonable end of range in the COUNTIF function. I've used down to row 100.