Selective Drop down List - Data Validation

vikrampnz

Board Regular
Joined
Jun 20, 2006
Messages
111
Office Version
  1. 2007
Platform
  1. Windows
Hello

I have a workbook that has two lists B2:B203 has "Unit Numbers" and C2:C203 has "Available" or "Sold" status in front of each unit.

I have created a drop down list showing "Unit numbers" in another worksheet using Data Validation. I am trying to figure out a way to only display "Unit Numbers" in the drop down list which are "Available" i.e. Have a status "Available" in the corresponding cell in column C.

For example:

Column B Column C
Unit No. Status
B-101 Sold
B-102 Available
B-103 Available
B-104 Sold .....and so on. Th drop down list showing Unit Numbers should only show Units: B-102 and B-103 as they are available.

Can anyone help please?

Thanks
V
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
It is not possible to 'filter' a list directly into a validation dropdown but there are ways around it.

You can either sort the existing list (manually or with vba) so that the available units are all at the top then use =OFFSET($B$2,,,COUNTIF($C$2:$C$203,"Available"),1) to generate the dropdown list or you can use formulas in the sheet to create a duplicate list of available units in another column then use that list for validation.

If you want to go with the method of creating a duplicate list then please either confirm that you are still using excel 2007 or update your profile to show the version that you are now using.
 
Upvote 0
Hello Jasonb75

Thanks for your quick response. Yes, I am using Excel 2007.

Thanks
 
Upvote 0
We will create another short list of "Available" units nd then use validation to display it.
 
Upvote 0
This is the formula to create the second list, enter it into an empty cell (I've assumed D2) then Array Confirm it by pressing Ctrl Shift Enter.

=IFERROR(INDEX($B:$B,SMALL(IF($C$2:$C$203="Available",ROW($C$2:$C$203)),ROWS($D$2:$D2)),"")

Once the array is confirmed, fill it down to D203 (again, assuming that you started in D2).

Then for the validation rule, use the formula =OFFSET($D$2,,,COUNTIF($D$2:$D$203,"Available"),1)
 
Upvote 0
Hello Jasonb75

Thanks for your help. The first part worked fine i.e. I was able to draw a smaller list of Available units. However, the drop down list (where I am using Data validation) is in another worksheet within the same workbook. Your second formula is giving me an error that says "you can not use references to other worksheets"

Can you please help

Thanks
 
Upvote 0
I've used this method with 2 sheets before without problems, try setting named range using the second formula as the definition, then refer to the named range in validation.
 
Upvote 0
What would I choose as Validation criteria ? I mean should I select "List" option from the "Allow" drop down or should I choose "Custom" option?
 
Upvote 0
Choose list, then enter the name of the range in there.

Sorry, I should have mentioned that earlier, if you had used custom then that might be why it didn't work with the formula.

If that doesn't work then I'll set up a test file to double check the steps.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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

Which adblocker are you using?

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
Back
Top