Filtering Drop down List to Only Show Some Values Based on Another Value in Adjacent Column

[Austeja]

Hi,

This is my first post, although I've been reading the forum for quite some time.

I have what I thought should be a pretty simple problem, but for some reason, I'm stuck on it and can't seem to find anything out there that explains my scenario exactly.


What I'm trying to do:

Basically, I have a spreadsheet with 2 tabs.

The first one has names of vendors (Company A, Company B, Company C, and so on) in column A and their status in Column B (let's say these are Approved, In-process, Not Approved). This is formatted as a table.The 2nd tab has another table that end users will be updating. In column B I have a dropdown list with the companies from tab1. (In data validation, list option, source references =NameOfSheet1!$B:$B )

I need this drop-down list to show only the companies that are approved. So it would work like this:

Tab 1
Company A - Status Approved
Company B - Status Approved
Company C - Status Approved
Company D - Status In-process
Company E - Status In-process
Company F - Status Not Approved

On tab 2, the dropdown would only show companies A, B, C. Bonus points if those would show up alphabetized regardless of their order in tab 1, but not necessary as I can always make sure they're sorted alphabetically when adding companies.



User Setup:
Everyone is on the 2016 version of Excel. We are sharing this file on SharePoint where end-users go to update. In addition, some users have Mac machines and will be updating the spreadsheet in the MS 360 Cloud Based service, where not all features are available. I don't know if macros would show up for them. Any thoughts?


I appreciate everyone's help on this and thank you in advance.

 

[MickG]

Try this in sheet2:-
The code is based on column A/B in Sheet1, and will update all Validation (DropDowns) in sheet2,When sheet2 is Activated.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Activate()
[COLOR="Navy"]Dim[/COLOR] RngDV [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ray() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] j [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] newVal [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] RngDV = Cells.SpecialCells(xlCellTypeAllValidation)
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1).Value = "Approved" [COLOR="Navy"]Then[/COLOR]
        ReDim Preserve ray(c)
        ray(c) = Dn.Value
        c = c + 1
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] i = 0 To UBound(ray)
    [COLOR="Navy"]For[/COLOR] j = i To UBound(ray)
        [COLOR="Navy"]If[/COLOR] ray(j) < ray(i) [COLOR="Navy"]Then[/COLOR]
            temp = ray(i)
            ray(i) = ray(j)
            ray(j) = temp
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] j
[COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]With[/COLOR] RngDV.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(ray, ",")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Austeja]

Thank you! Does macro functionality transfer over to Mac users? I can test this on Monday with a Mac user.

Also would like to know, for my own development, is this possible without a macro? Thanks again.

In addition, some users have Mac machines and will be updating the spreadsheet in the MS 360 Cloud Based service, where not all features are available. I don't know if macros would show up for them.