DATA VALIDATION WITH 'ALL' OPTION OR WILDCARD

[KDS14589]

Excuse me if I don't post the worksheet in question. To make a sanitized copy because of sensitive information would be complicated so here is a simplify sample what I'm attempting.

Worksheet #1 has 3 columns each 100 rows long. 'A' column (which I'll call 'categories', in real life no name) has only 8 cells with data. Column 'B' (I'll call 'item') has data in all 100 cells. Column 'C' has a dynamic data validation list from data in Column 'A' that expands for new list while ignoring blanks. So, data in column 'C' is the category that data in 'B' belongs to.

Worksheet #2 3 columns again
Column 'A' is blank. Column 'B' shows data from WS#1 Column 'B' and Column 'C' shows data from WS#1 Column 'C' which matches WS#1 Column 'C' data to a cell that has a choice of 'Categories' from a data validation list from column 'A' WS#1 in which I can choose which Category I wish to show.

What I'm attempting is to have a choice in the validation list on WS#2 to have an option to list all entries and 'Categories', this way the user does not have access to WS#1. I've tried wildcards but they don't work with data valuation, unless I'm doing something wrong. This part of the program is still in its infancy so I can redesign it if needed. Any help or comments would be appropriated.

 

[Toadstool]

Hi KDS14589,

It's difficult to follow how your Data Validation list is to be built without sample data so I'll just point you at my reply to a similar request: Dropdown - list of inventory items based on a sale date

The crux is that you build your LoV using whatever criteria needed then use OFFSET and COUNTIF as the source to point at the built list.

 

[KDS14589]

Hi KDS14589,

It's difficult to follow how your Data Validation list is to be built without sample data so I'll just point you at my reply to a similar request: Dropdown - list of inventory items based on a sale date

The crux is that you build your LoV using whatever criteria needed then use OFFSET and COUNTIF as the source to point at the built list.

Hi KDS14589,

It's difficult to follow how your Data Validation list is to be built without sample data so I'll just point you at my reply to a similar request: Dropdown - list of inventory items based on a sale date

The crux is that you build your LoV using whatever criteria needed then use OFFSET and COUNTIF as the source to point at the built list.

I'm not very good at sharing files online but here goes......good luck....let me know if it did not work.....link...https://www.dropbox.com/s/n8nqzfcdspw8p3z/mr.excel2%2009.12.20.xlsx?dl=0
excuse me if I don't share the original workbook/sheet I'm working on a program for my clients which are mentally disabled and I don't want to upset any of them if they find out that I shared the program with anyone even with out their info. (their very paranode) this file is basically the same as needed (same format, same formulas) I went onto the link you had and incorporated some ideas. Any help would still be appreciated

 

[Toadstool]

Yes the link worked fine.
I see what you've done and I'd have the OFFSET on the Data Validation Source but you're using it to amend the Name which seems to work OK in building the list for column B.
...but I'm still confused as to what you want to do?

 

[KDS14589]

on Sheeet2 I want the dropdown list for the data validation in E3 to show ALL the available 'Categories' (presently 9) plus a choice to show 'All Categories'

 

[Toadstool]

on Sheeet2 I want the dropdown list for the data validation in E3 to show ALL the available 'Categories' (presently 9) plus a choice to show 'All Categories'

Sorry but I just don't understand.

 

[KDS14589]

Sorry but I just don't understand.

The listbox in Sheet2!$E$3 lists the available 'Categories' from Sheet1!$A1:$A$100 to be used as a criteria in the formula in Sheet2!$B$1:$B$100. This in turns allows Sheet2!$B$1:$B$100 to display the contents from Sheet1!$B$1:$B$100 that corresponds with the 'Categories' in Sheet1!$C$1;$C$100 that match the selection in the listbox at Sheet2!$E$3 I want the listbox to have another option to show ALL the contents of Sheet1!$B$1:$B$100 into Sheet2!$B$1:$B$100