Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Validation based on another column 1

Status
Not open for further replies.

grnzbra

Programmer
Mar 12, 2002
1,273
US
I have a two column lookup table with the column names EquipmentType and EquipmentSubType The EquipmentType column contains entries like "AIR CONDITIONER" and "VEHICLE". The corresponding values in the EquipmentSubType column are "WINDOW AIR CONDITIONER" and "ROOFTOP AIR CONDITIONER" for "AIR CONDITIONER" and "PANEL TRUCK" and "SCHOOL BUS" for "VEHICLE". In my main worksheet, I have a table with the same column names which validate on the corresponding columns in the lookup table. What I need is the EquipmentSubType selection to show only the values corresponding to those selected for the EquipmentType. If "VEHICLE" is selected, the EquipmentSubType validation list should only have "PANEL TRUCK" and "SCHOOL BUS" in the validation selection. What I get now is the entire list, "WINDOW AIR CONDITIONER", "ROOFTOP AIR CONDITIONER", "PANEL TRUCK", and "SCHOOL BUS"

How can I make the validation selection list show only Sub Types corresponding to the selected Type
 
 http://files.engineering.com/getfile.aspx?folder=b7b033fc-88c4-44c5-aece-b76fa6d3ef75&file=LookupTable.jpg
grnzbra,

A picture is next to worthless if you're going to upload anything. So what happens in vagueness stays in vagueness.

First you'll need a list of unique values of EquipmentType. That can be accomplished using MS Query (FAQ68-5829) using your table as source and SQL like
[pre]
Select DISTINCT EquipmentType
From [YourSheetName$]
[/pre]
The result of that query can be used as the source list for an in-cell Data > Validation Drop-Down.

Once a values is selected that selection value can be used as a criteria value for the second query as a source list for a second Data > Validation in-cell drop down,,,
[pre]
Select EquipmentSubType
From [YourSheetName$]
Where EquipmentType = ?
[/pre]
...and set the Parameter to the cell containing your first selection.

Its just that simple!

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
An alternate solution would be using this formula in the second Data Validation LIST option...
[tt]
=OFFSET(
EquipmentType,
MATCH(YourEquipmentTypeSelection,EquipmentType,0)-1,
1,
COUNTIF(EquipmentType,YourEquipmentTypeSelection),
1)
[/tt]
I also used Named Range references.


Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Thank you, SkipVought
The first solution worked magnificently...until I found that the query had to be saved as a separate file. Unfortunately, this has to be totally self contained in the .xlsm file.

Have been trying to work with the second solution, but can't seem to figure out where it goes? Does it go into the Source field of the Data Validation window? Or is it supposed to go into a third field in the Lookup table? I am assuming that YourEquipmentTypeSelection refers to a single cell (let's say $D$1) that receives the value selected from the EquipmentType drop down. Is this correct?
 
until I found that the query had to be saved as a separate file."

You do not need to save the query as a separate file. I have used MS Query often for some 20 years, and I have NEVER saved a query as a separate file. The query will be saved with the workbook.

Regarding Data Validation, Allow must contain List and Source must contain the Offset() formula.

BTW, in my workbook applications I make extensive use of Nmed Ranges. Rather than $D$1 in a formula reference, a named range like, SelectedEquipTyp migh be more understandable and maintainable.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
SkipVought said:
BTW, in my workbook applications I make extensive use of Nmed Ranges. Rather than $D$1 in a formula reference, a named range like, SelectedEquipTyp migh be more understandable and maintainable.

So SelectedEquipTyp would refer to $D$1?

So, when you get to the end of creating the query and you get the choice of Show Values or Save Query, what do you do?
 
From the FAQ, once you have composed your query, this on is a parameter query, hence the ?, you...
"File > Return data to Microsoft Excel."
...and finally point the parameters to the appropriate cell, like D1.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Thank you for your patience. I'm usually working with SQL Server and have rarely done anything with Excel.

I've gotten the query for the EquipmentType and am close on the EquipmentSubType. My problem is the WHERE clause. Starting with the SELECT clause generated by Excel, I've added the WHERE clause formatted in what looks to me, like the same format as the rest of the statement. But i get this:

SubTypeError_lvx0wx.jpg


The SQL statement reads:
Code:
SELECT `AssetSubTypeLookup$`.AssetType, `AssetSubTypeLookup$`.AssetSubType
FROM `AssetSubTypeLookup$` `AssetSubTypeLookup$`
WHERE 'AssetSubTypeLookup$'.AssetType = 'Equipment$'!$D$1

When I select OK, I'm told
When I seiect HELP, the instructions don't seem to give any info about the SQL Statement syntax. I've tried using the SelectedAssetType, the named range for Equipment!$D$1. I've tried it with the period instead of the exclamation mark. I've tried it without the quotes. What am I doing wrong?
 
Code:
WHERE 'AssetSubTypeLookup$'.AssetType = ?

You need to literally enter a QUESTION CHARACTER in your SQL!




Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
BTW, have you arrived at a solution?

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top