Searchable Drop Down on Different Sheet

**catnam** · 01-22-2016, 07:25 PM

I have a workbook in which I have created a searchable drop down list, on the LIST MANAGER sheet. Currently the Searchable Drop Down list only appear in one cell (Yellow cell) on the LIST MANGER sheet. I followed this video https://www.youtube.com/watch?v=vkPoViUhkxU to create what I have so far.

But I need help to have the same drop down list to appear in EVERY cell in the NAME column, of the INCOME section, on January budget.
I don't need the drop down in the yellow cell.

I should then be able to use that to complete the searchable drop downs for Living Expense and Misc-Expense sections.

I really appreciate your help

**FDibbins** · 01-23-2016, 12:34 AM

If you want the DD in the rest of the cells, just copy it down? Not really sure what you are trying to do there, seems like you are going in circles - the DD references a range that gets its data from the DD?

**catnam** · 01-23-2016, 02:19 AM

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.

**windy58** · 01-23-2016, 04:24 AM

Have you looked at the follow up video?

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

Windy

**JohnTopley** · 01-23-2016, 05:01 AM

See attached.

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

**catnam** · 01-23-2016, 08:21 AM

No. Please read my post below. You cannot simply copy and paste. The cells under NAME are still not Dynamic or searchable.

**catnam** · 01-23-2016, 08:24 AM

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

**JohnTopley** · 01-23-2016, 01:29 PM

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.

**catnam** · 01-23-2016, 02:31 PM

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

**FDibbins** · 01-23-2016, 04:52 PM

Let me see if I understand you correctly here.

1. You kind of have what you want on LIST sheet, in the yellow cell - you enter a "filter", and you get a list of names matching that filter.
2. you want the same feature on Jan sheet (and the others), but you don't want to have a cell to enter the filter criteria?

If I am correct with this, how would you create your filtered names list without the criteria?

Also, this...

Note, if you select the first item in column B on LIST MANAGER sheet, you will see the first reference is $C$7

The 1st used cell in List is B9, and that contains...
=IF(ISNUMBER(SEARCH($C$7,$C$9:$C$149)),MAX($B$8:B8)+1,0)

Perhaps if you walked us through what you actually want, instead of trying to patch something together from video's, it might be easier to help

**JohnTopley** · 01-23-2016, 05:00 PM

You might need the follow on ....

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

**catnam** · 01-23-2016, 06:38 PM

Hi FDibbins,

All I'm trying to do is create a "searchable" drop-down list in the cells (A14:A94) on JAN Budget sheet, using the Income-Payer list on LIST sheet as source.

I also need a "searchable" drop-down list in all cells (H14:H94) on JAN Budget sheet, using the Expense Payee list on LIST sheet as source.

Finally, I need a "searchable" drop-down list in all cells (O14:O94) on JAN Budget sheet, using the Misc-Expense list on LIST sheet as source.

What I mean by "searchable" is that you enter just a few characters into the input field (for example: "sp"), then click the drop-down arrow, and only items from the list containing "sp" are presented as selections. For example, if spouse, spending and special (alone with other entries) are all in the list, only those 3 will be presented as selections. The "sp" is the criteria entered and used in the initial search.

I know how to do simple data validation, by naming the individual list, then using that name as source. But that drop down list will display all entries and you must scroll the entire list the get what your want. Whereas with the 'searchable" drop down, you first enter the criteria or whatever letters you know are in what you are looking for, and only those items are presented as selections.

I've attached a fresh and new workbook "Budget Worksheet2"

I hope I'm being clear about what I'm trying to do. Sorry for any confusion.

**windy58** · 01-24-2016, 05:24 AM

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

See attached

Windy

**JohnTopley** · 01-24-2016, 05:56 AM

@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?

**windy58** · 01-24-2016, 07:47 AM

John

I just type in the cell then click the handle

Windy

**JohnTopley** · 01-24-2016, 11:31 AM

My attempt.

In A10 in "JAN BUDGET"

=CELL("Contents")

in A14 and down: set Data Validation

=Income_Payers

Set "Error Alert" OFF

in "LIST MANAGER"

in A1 tab name e.g "JAN BUDGET"

B9 and down

=IF(ISNUMBER(SEARCH(INDIRECT("'" &$A$1&"'!A10"),'LIST MANAGER'!$C9)),1+MAX($B$8:B8),0)

in C9 and down

=IFERROR(VLOOKUP(ROWS($1:1),$B$9:$C$149,2,0),"")

This named range "Income_Payers"

Need VBA "Worksheet_Change" on each monthly tab to set A10 in "List Manager" to tab name.

Hopefully you can then simply replicate the "JAN BUDGET" field in all other tabs.

**windy58** · 01-24-2016, 01:29 PM

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

**catnam** · 01-24-2016, 06:20 PM

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

**catnam** · 01-24-2016, 06:35 PM

Thanks John. replicating this for JAN Living Expense and Misc_Expens; and on the other tabs is just what I intend to do. I think I can figure it all out now.

But please explain how do I do this: "Need VBA "Worksheet_Change" on each monthly tab to set A10 in "List Manager" to tab name.

Really appreciate your help.

**JohnTopley** · 01-25-2016, 03:12 AM

Your VBA is password protected: if you can post a workbook (return the last one I posted) with the password removed, I will add the code and tests that it works!

If you are only using a single month at one time, you simply type the tab name into A1 rather than use VBA to do it.

**catnam** · 01-25-2016, 04:05 PM

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

**JohnTopley** · 01-25-2016, 04:41 PM

Please Login or Register  to view this content.

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

**catnam** · 01-26-2016, 02:00 PM

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.

**JohnTopley** · 01-26-2016, 02:07 PM

To insert code:

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

**catnam** · 01-26-2016, 05:33 PM

Thank much!

Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Re: Searchable Drop Down on Different Sheet

Thread Information

Users Browsing this Thread

Similar Threads

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions