Data validation lookup

[Dave]

Is there a way to have a drop down list using data validation in file
"jobs.xls" sheet "Sheet 1" Cell C4 to select a contact from a list that is
in the file "parts.xls" sheet "contact" and fill in the corresponding cells
in "Jobs.xls". The list of contacts starts on row 3 and goes to row 48 now
but there will be more in the future. If there is another way to do this
other than data validation I am open to other ideas.



In "Job.xls" Sheet name is "Sheet 1"



C4 C5 C6 D6 E6 C7 D7 C8 C9

Contact Address City St Zip Phone Fax Map e-mail





In "Parts.xls" Sheet name is "contact"



B3 C3 D3 E3 F3 H3 I3 J3 K3
Contact Address City St Zip Phone Fax Map e-mail



Thanks

Dave

 

[Max]

One possible approach to set it up ..

Open *both* files: Parts.xls and Jobs.xls

In Jobs.xls, in a new Sheet2
---------------------------------------
Put in A1:

=IF(ISBLANK([Parts.xls]Contact!B3),"",[Parts.xls]Contact!B3)

Copy A1 across to I1,
and down to say I100
(for a initial capacity of 100 contacts?)

This will link to / extract the data from Parts.xls

Click Insert > Name > Define
Enter in the "Names in workbook" box: Contact
Put in the "Refers to" box:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
Click OK

The above creates a dynamic range* called "Contact"
for use in the DV list we're going to put in C4 in Sheet1

*For more on dynamic ranges,
do check out Debra D's nice coverage at:
http://www.contextures.com/xlNames01.html#Dynamic

In Jobs.xls, in Sheet1
------------------------------
Select C4

Click Data > Validation
In Settings tab
---------------------
Under "Allow:" select List
Put in "Source:" box: =Contact
Click OK

This'll create the Contacts DV list for selection in C4

The rest of the info in the other cells (as per your config)
can then be pulled-in using OFFSET(.. MATCH(..)..)
reading what's selected in C4

Put in:

C5: =OFFSET(Sheet2!$A$1,MATCH($C$4,Sheet2!$A:$A,0)-1,1)
C6: =OFFSET(Sheet2!$A$1,MATCH($C$4,Sheet2!$A:$A,0)-1,2)
C7: =OFFSET(Sheet2!$A$1,MATCH($C$4,Sheet2!$A:$A,0)-1,5)
C8: =OFFSET(Sheet2!$A$1,MATCH($C$4,Sheet2!$A:$A,0)-1,7)
C9: =OFFSET(Sheet2!$A$1,MATCH($C$4,Sheet2!$A:$A,0)-1,8)

D6: =OFFSET(Sheet2!$A$1,MATCH($C$4,Sheet2!$A:$A,0)-1,3)
D7: =OFFSET(Sheet2!$A$1,MATCH($C$4,Sheet2!$A:$A,0)-1,6)
E6: =OFFSET(Sheet2!$A$1,MATCH($C$4,Sheet2!$A:$A,0)-1,4)

The last number in the OFFSET above (i.e. 1,2,5,7...)
is the cols parameter (Note: Height and Width params omitted)
which determines the col in Sheet2 to pull the info from

 

[Max]

A slight revision ..

Instead of

Put in the "Refers to" box:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

Maybe try this revised formula for the DV dynamic range ..

Put in the "Refers to" box:

=OFFSET(Sheet2!$A$1,0,0,SUMPRODUCT((Sheet2!$A$1:$A$65535<>"")*1),1)

This seems to work ok for a "neater" DV droplist in C4 in Sheet1