data lookup/validation syntax

  • Thread starter Thread starter Doug Howell
  • Start date Start date
D

Doug Howell

In Excel 2007 I have a sheet "sheet1" which has a part number in a
cell. (A5)
I want cell (D5) to have a data validation drop down menu which is
populated from "sheet2".

Sheet2 data looks something like this:


Column A Column B
partnumber1 tradename1
partnumber1 tradename2
partnumber1 tradename3
partnumber2 tradename4
partnumber3 tradename5
partnumber3 tradename6
etc etc etc...........


The drop down in (D5) sheet1 should only contain tradenames which
have
partnumber matching (A5) sheet1.


Ideas?
Can I use SQL syntax in the data validation lookup?
 
This was so easy in versions of Excel prior to Excel 2007!

Here you go...

Since the source data is on a different sheet you'll have to create a
defined dynamic range.

Let's assume the source data on Sheet2 starts in cells A2:B2 and goes down
to A100:B100.
Column A Column B
partnumber1 tradename1
partnumber1 tradename2
partnumber1 tradename3
partnumber2 tradename4
partnumber3 tradename5
partnumber3 tradename6
etc etc etc...........
Click to expand...

Create the dynamic range...

Ribbon>Formulas tab>Defined Names>Define Name
Name: TradeNames
Refers to:

=OFFSET(Sheet2!$B$2,MATCH(Sheet1!$A$5,Sheet2!$A$2:$A$100,0)-1,,COUNTIF(Sheet2!$A$2:$A$100,Sheet1!$A$5))

OK

Setup the data validation list...

On Sheet1 select cell D5
Ribbon>Data tab>Data tools group>Data Validation (top right icon)
Allow: List
Source: =TradeNames
OK
 
Back
Top