DATA VALIDATION WITH 'ALL' OPTION OR WILDCARD

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
996
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jan 10, 2019
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
996
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jan 10, 2019
Messages
54
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 5, 2018
Messages
996
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jan 10, 2019
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,073
Messages
5,545,832
Members
410,709
Latest member
Mrsamir
Top