Drop Down List and Data Validation

[scr1b3]

Hi,

I have been working on this for while and hoping someone here can help me with a formula.

On sheet1 I have a drop down list (column B) of serial numbers from Sheet2 column B. On sheet1, when I select a serial number, the Status column (Sheet2 column A) automatically changes from 'Available' to 'In Use' using this formula:

=IF(ISNA(MATCH(B2,Sheet1!$B$2:$B$21,0)),"Available","In Use")


Now on Sheet1, how do I make the drop down list to only list serial numbers that are 'AVAILABLE' from Sheet2?

[sourabhg98]

In sheet2 create a column C which shows values which are available using array formula (to be entered with CTRL+SHIFT+ENTER instead of just ENTER)-

Please Login or Register  to view this content.

Now in Sheet1 you can create the dropdown with column C as the range.

[ChemistB]

To do this, we create a secondary list which you use for your data validation.
In Office_Keys!$D$1:$D$22, In D2 (I like to start my lists with a blank value but you can start in D1 if you like)

this ARRAYED Formula
=IFERROR(INDEX($B$2:$B$22, SMALL(IF($A$2:$A$22="Available", ROW($A$2:$A$22)-ROW($A$2)+1), ROWS($A$1:$A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then I created this dynamic named range called List
=Office_Keys!$D$1:INDEX(Office_Keys!$D$1:$D$22,SUMPRODUCT(--(LEN(Office_Keys!$D$1:$D$22)>0))+1)

I used "List" in the Data Validation in Inventory.

[scr1b3]

Thank you so much!!! could have never figure this out myself.