Searchable Drop Down on Different Sheet

What I'm trying to do is have all the cells in the NAME column on JAN BUDGET to have a searchable drop down list. I need them all to behave just as the yellow cell behaves. If you enter "sp" in yellow cell, a list of all entries with "sp" in them is generated. I was following another video to get what I have so far. However, I don't know how to make drop downs appear (on another sheet-JAN BUDGET) in all cells under NAME. Simply copy and paste does not work. Thanks. Appreciate your help.

Have you looked at the follow up video?

https://www.youtube.com/watch?v=0QrQT9D25Xk

Windy

Windy, Yes I've looked at that video. But he put all those yellow cells on same sheet. I need them on different sheet.

See attached.


Is this what you require? If so, done by Copy/paste.

Each cell under NAME has the following:

Spouse2Bonus
Spouse1Bonus
Spouse1Net
Spouse2Net


Isn't that what you want?. Click on any NAME cell and you will get the drop down list.

John, I’m trying to replicate what you see in this video https://www.youtube.com/watch?v=vkPoViUhkxU , however, instead of having the multiple cells (the 15 or so yellow highlighted cells) on the same sheet, I need them on a separate sheet. In my case in need them on the Jan Budget Sheet.

Please do read my first two posts to help understand what I’m trying to do. But let me explain further. The drop down list (which you cited) under NAME, on JAN BUDGET, in INCOME section is incorrect. It is not working properly as a ‘searchable drop down’ should. I want it to work just as the yellow cell on LIST MANAGER sheet does. Please see the yellow cell on LIST MANAGER sheet. It currently has “sp” in it. Try entering the following alternative text: “ac”, in yellow cell, then click the down arrow. You then see a drop down list that only show values from column C which has “ac” in the text. Also the list in columns E and F change.
When everything is buttoned up, column C, E & F will be hidden and somehow I don’t need that single cell (C7), on LIST MANAGER sheet that currently had a properly working ‘searchable drop down”. This single cell needs to be replaced by multiple cells under NAME on JAN Budget.

If I can get the cells under NAME on JAN BUDGET to work correctly, as demonstrated by the yellow cell, then that is what I want. Remember, I will not need the yellow cell to remain after the drop downs under NAME are working correctly. All I’m trying to do is create ‘searchable drop down’ in all the cells, where if you type a few letters of a word (that’s in the list in column C on LIST MANAGER), then click down arrow, all words matching the letters appear in the drop down. That way you don’t have to scroll through all items in the available items in list (column C).

Note, if you select the first item in column B on LIST MANAGER sheet, you will see the first reference is $C$7. I think somehow the formula needs to refer to cell A14, on JAN BUDGET. But everything I tried did not work. Also, I created the name (SearchIncome) to use in setting data validation for cells under NAME.

Thanks for your help

You might need the follow on ....


https://www.youtube.com/watch?v=0QrQT9D25Xk

I followed the second video, and then used the named range for the validation on Jan sheet.

See attached

Windy

@windy

I set up a sheet as per the video ( I believe!) but when I click on a cell, the drop-down handle appears immediately (which was not the case in the video)and I cannot enter data. What I am doing wrong?

John

I just type in the cell then click the handle

Windy

Hi John

Really not sure what is happening.

Using your attachment I have done the following...

Cell C7 in List Manager
=CELL("Contents")

Adjusted Income_Payers named range to read
=OFFSET('LIST MANAGER'!$D$9,0,0,COUNTIF('LIST MANAGER'!$D$9:$D$149,"?*"))

Then selected all the cells in Jan A15:A94, added data validation
List
Source:
=Income_Payers

This seems to be working.

Windy

Windy, I can't THANK you enough. My workbook now works just as I need. Thanks so much to you, John, FDibbins.

Hi John, I've remove password and attached the workbook you modified. I will be using just one month at a time, but I have 12 months. Thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Or Target.Column = 8 Or Target.Column = 15 Then
            Sheets("List Manager").Range("A1") = ActiveSheet.Name
    End If
End Sub

Sets A1 in "List Manager" to current (Active) worksheet name if anything is entered in "NAME" columns

This is the FINAL version. Only the JAN, FEB, and MAR budget have had validation set in NAME column, in Income, Expense and Misc_Expense section.

To insert code:

Right click on tab==>"View Code"==>Copy/paste code (same for all sheets)

Thank much!