Selective Drop down List - Data Validation

vikrampnz

Board Regular
Joined
Jun 20, 2006
Messages
92
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
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.
 

vikrampnz

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

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

Thanks
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
Which method do you want to use?
 

vikrampnz

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

ADVERTISEMENT

We will create another short list of "Available" units nd then use validation to display it.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
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)
 

vikrampnz

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

ADVERTISEMENT

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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
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.
 

vikrampnz

Board Regular
Joined
Jun 20, 2006
Messages
92
Office Version
  1. 2007
Platform
  1. Windows
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?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,037
Messages
5,639,670
Members
417,104
Latest member
Nelsini

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
Top