Conditional Inclusion of a Drop-Down List

[expos4ever]

I will illustrate my challenge by way of a simple example. Suppose I have a list of 25 baseball player names (A1 through A25) on sheet 2. Suppose, further, that I create a "yes-no" drop down in each cell in the range B1:B25 (sheet 2) - this lets me choose which players go in today`s lineup. On another sheet (sheet 1), I use a trick I learned from someone to present a single (column-based) list of the players I have selected for today's lineup with no "blanks" - one single column identifying all players with a "yes" from the first sheet.

This trick is based on the use of a formula that I found (uses INDEX, SMALL, ROW functions). Here is the formula for my actual problem (the baseball player thing is a simplified example):

=IFERROR(INDEX(Sheet2!$D$1:$D$4,SMALL(IF(Sheet2!$B$1:$B$4<>Sheet2!$E$1,ROW(Sheet2!$D$1:$D$4)),ROW(Sheet2!1:1)),1),"")

With this as background and returning to the baseball example, I would like to have sheet 1 make another drop-down list available for each selected player (e.g. what position they will play); I can then make the appropriate position selections on sheet 1. I think the way to do this is to create a column of 25 cells with this new "position" drop down list of sheet 2, and then figure out how to selectively copy the position drop-down list (to sheet 1) for only those players selected to go in the line-up. Hope that is clear.

In short, I fear that it is impossible to copy a drop down list (the list itself, not a selection from that list) from one cell to another using a formula.

Any advice appreciated.

 

[mumps]

I think that you will need a macro to do what you want. Do you have a list of positions on a sheet somewhere? If you do, on which sheet and in what range?

 

[expos4ever]

I think that you will need a macro to do what you want. Do you have a list of positions on a sheet somewhere? If you do, on which sheet and in what range?

My actual problem is a little more complicated than the baseball player scenario. Instead of a list of baseball players, I have a list of activities, organized by category. For example, I have something like this in column A of sheet 2:

Category 1 Events
a
b
c
Category 2 Events
d
e
f
Category 3 Events
g
h
i
j

Per another thread (that you responded to), I have successfully implemented a system whereby in column B of sheet 2, I have a yes-no drop-down list so that I can choose which categories and activities within categories get presented in a single gap-less list on sheet 1. So suppose I select Yes for everything in the above list except b,c,e,f, g,h. Then, as desired, I get this on sheet 1:

Category 1 Events
a
Category 2 Events
d
Category 3 Events
i
j

What I would like to do now is this: just as I have selectively copied activities (and categories of activities) from column A of sheet 2 based on the yes-no value in sheet 2, column B, I would like to selectively copy a drop-down list from column C of sheet 2 that represents the "degree of completion" of a task (not started, underway, complete). However - and this is where my real problem is more complicated than the baseball example - I only want that drop-down list to be present for a selected activity (such as "a") but not for a selected category (e.g. Category 1 Events).

Does that tell you what you need to know? Thanks again for your help.

 

[expos4ever]

I seem to be unable to edit my previous post - please replace the word "activity" with the word "event".

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes the macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples form your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[expos4ever]

Hi mumps. Tried to make the sheet "non-confidential" - somehow changed functionality in the process. This is getting too complicated for me, and my boss would not approve of me spending too much time tinkering with excel. I will use an ugly brute force approach. Too bad excel is so complicated - I guess this is an unavoidable consequence of the rich functionality. Thanks, however, for your interest.

 

[mumps]

I found this code a while back created by Rick Rothstein. Try it on a copy of your file. It should de-sensitize text only in your workbook. It may do what you want if you have time to give it a try and look at the results. Place the code in a regular module and run the "Obfuscate" macro.

Sub Obfuscate()
' Created by Rick Rothstein
  Dim X As Long, WS As Worksheet, Cell As Range
  Dim Txt As String, UpperLetters As String, LowerLetters As String
  Randomize
  UpperLetters = Join(RandomizeArray(Split(StrConv("ABCDEFGHIJKLMNOPQRSTUVWXYZ", vbUnicode), Chr(0))), "")
  LowerLetters = Join(RandomizeArray(Split(StrConv("abcdefghijklmnopqrstuvwxyz", vbUnicode), Chr(0))), "")
  Application.ScreenUpdating = False
  For Each WS In Sheets
    For Each Cell In WS.Cells.SpecialCells(xlConstants, xlTextValues)
      Txt = Cell.Value
      For X = 1 To Len(Txt)
        If Mid(Txt, X, 1) Like "[A-Z]" Then
          Mid(Txt, X) = Mid(UpperLetters, Asc(Mid(Txt, X, 1)) - 64, 1)
        ElseIf Mid(Txt, X, 1) Like "[a-z]" Then
          Mid(Txt, X) = Mid(LowerLetters, Asc(Mid(Txt, X, 1)) - 95, 1)
        End If
      Next
      Cell.Value = Txt
    Next
  Next
  Application.ScreenUpdating = True
End Sub

Function RandomizeArray(ArrayIn As Variant) As String()
' Created by Rick Rothstein
  Dim Cnt As Long, RandomIndex As Long, Tmp As String
  Randomize
  For Cnt = UBound(ArrayIn) To LBound(ArrayIn) Step -1
    RandomIndex = Int((Cnt - LBound(ArrayIn) + 1) * Rnd + LBound(ArrayIn))
    Tmp = ArrayIn(RandomIndex)
    ArrayIn(RandomIndex) = ArrayIn(Cnt)
    ArrayIn(Cnt) = Tmp
  Next
  RandomizeArray = ArrayIn
End Function