Data validation lookup

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


0
ledcomp (18)
7/16/2004 12:16:04 AM
excel 39879 articles. 2 followers. Follow

2 Replies
265 Views

Similar Articles

[PageSpeed] 6

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

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"Dave" <ledcomp@yahoo.com> wrote in message
news:LNmdnU3onOykg2rd4p2dnA@adelphia.com...
> 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
>
>


0
demechanik (4694)
7/16/2004 3:21:46 AM
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
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----


0
demechanik (4694)
7/16/2004 3:53:44 AM
Reply:

Similar Artilces:

format based on data from another cell
I want the names in column B of my spread sheet to highlight either red or yellow based on the value of the cell in column L, but with conditional formatting you can only format based on the value of the cell you are formatting, as far as i can tell. Hi Sanna conditional formatting can be used to do this, select the column B cells that you want the formatting to be applied to, ensure that the first selected cell is the first line visible at the top of the worksheet choose format / conditional formatting choose formula is type =$L2=x where row 2 is the first row in the selected column an...

Data Format: how can I re-format
The spreadsheet that was exported for me to use has the information in paragraph form: It is customer ID name and billing info but it is like a long series of address labels. I want to be able to have them listed under captions like company name, contact, address, city, etc.. so I can use the data. There is an empty row between each entry. Each entry is 3 or 4 rows. There are about 500 records so i dont want to manually set them up. Thanks Try tinkering around with these steps Assuming your list is in col A, data in A2 down (If data starts in A1 down, insert a new row for the co...

Count invalid data entries
I am using the following macro to identify invalid data entries in cells with data validation applied. Sub CheckOrder() ' Application.CommandBars("Formula Auditing").Visible = True ActiveSheet.CircleInvalid Sheets("Configuration").CircleInvalid Sheets("Parts_TakeOff").CircleInvalid MsgBox ("Check for Red Circled Invalid Data Entries on" & Chr(10) & " Configuration and Parts_TakeOff Sheets") End Sub Is there a way to count the number of invalid data entries (red circles) and write the number to a particular ce...

Display comments as data #2
thanks a lot , this worked exactly as required, the comment go converted to data Now just one more thing . is the vice versa possible i.e if i have dat in column A can it be shown as comment in column B. the reason i need this is bcos i am shifting some data with thei comments to MS access(Hence the need to convert omments to data) processing them and again converting them to excel (the need to conver data back to comments -- sonik ----------------------------------------------------------------------- sonika's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=...

auto generate emails from data stored in excel spreadsheet
Hi, Is there a way in excel to automatically generate an email which is stored in a worksheet either using excel macros or in conjuction with outlook. thanks in advance. Mark K Look here http://www.rondebruin.nl/sendmail.htm Maybe this one http://www.rondebruin.nl/sendmail.htm#message -- Regards Ron de Bruin http://www.rondebruin.nl "MarkK" <anonymous@discussions.microsoft.com> wrote in message news:169b901c447f2$268f26b0$a001280a@phx.gbl... > Hi, > Is there a way in excel to automatically generate an > email which is stored in a worksheet either us...

Merging data from two worksheets
I have two worksheets with lists on them. On worksheet A there are items we use, and charge code (which correspond to individual items); but there is no RMS code (a specific code we use for our system). On worksheet B there is a larger list of items, many of which are also on worksheet A. Worksheet B has a column with the same charge codes as are used in worksheet A, but worksheet B has the corresponding RMS codes. For example... On A: Item RMS Charge Code Stick (blank) 12345 Ball (blank) 54321 Bases (blank) 21543 On B: Item RMS Charge...

Exchange data backup
I'm worried emails backup of the small office. Does SBS2008 Exchange server has a direct backup capability of whole mail database? How to backup every night Emails from server to external harddisk? Does Exchange server needs to be stopped before backup? Thanks SBS2008 Takes care of it. http://blogs.technet.com/sbs/archive/2008/11/03/introducing-sbs-2008-backup.aspx look at the comments section Russ -- Russell Grover - SBITS.Biz [SBS-MVP] MCP, MCPS, MCNPS, SBSC Microsoft Certified Small Business Specialist 24hr SBS Remote Support - www.SBITS.Biz Question or Seco...

Data Save error
GP Ver 6.0. Recently I am facing an error with normal users unable to save records, where as the system administrator is able to save the same information. Please advice on this error, what has to be done. I have disabled the windows user ID of the Database owner. Thinking of this made Dynasa as the DBO. Database is having enough free space available. Hi How big is the company database? Is it over 10GB? Regards James "Matthews" <Matthews@discussions.microsoft.com> wrote in message news:3FF3F553-5B18-4D8A-B65E-8A253BF94BB2@microsoft.com... > GP Ver 6.0. Recently I...

Extracting XML data to be used in XSLT HTML output
Ok: I have done small level Xml stuff and am just now starting to understand some of the plumbing involved. I've done a lot of .NET programming for SQL Server, but little for Xml data stores. I am currently just interested in using the .xsl file that I have being applied to a source .xml file through ASP classic. Here's what I have: XSL file ---------------------------------------------------------------------------- --- <?xml version="1.0" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output meth...

Pivot Table not pulling all fields or data
Hi All........ Without getting too verbose, I have a 900 row by 26 column database I am applying a Pivot Table to. The code seems to run fine, but it is not returning some of the fields, and of course the related data. I have tried reformatting the entire columns I am pulling from but no joy......however, when I reformat an errant cell from General to TEXT, that row comes in........and/or, if I change the value in one of the missing cells, it will bring that row in. The code runs fine, and was mostly derived by recording a macro in XL2k, but the data cannot be trusted becau...

How do I automatiicaly redefinie Source data range for a chart?
I have A Chart Defined by Category (X) values in Collumn B and Y values in Collumn C. Right now Both are defined by rows 4 to 114. I would like to be able to have the chart automatically redefine itself so if I put a value in C115 the chart would automaticaly set Y value range to be C4:C115 eithout having to manually define chart. Is this possible? If so how? I've tried OFFSET bu so far no luck. Hi, If you turn your data cells into a List, or Table in xl2007, the chart will auto expand. The named range approach will work if you set up the names correctly. See here for more info...

overflow data
When i input data into a cell, can i fix it so it doesn't overflow the adjacent but empty cell? Thanks. You could enter a space character in the adjacent cell, so it appears empty, but will stop the overflow. Barbara wrote: > When i input data into a cell, can i fix it so it doesn't > overflow the adjacent but empty cell? Thanks. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html thanks, Genius! :-) >-----Original Message----- >You could enter a space character in the adjacent cell, so it appears >empty, but will ...

external data not in a table
I have a worksheet originally created in excel2003 that gets external data from a db2 database. After migrating to excel2007 the external data in the worksheet is not in a table (clicking on a result cell does not make Table Tools appear). When creating a new database query I find that the results always get put in table. I don't want the results in a table. How do I make the results of a new query NOT be a table? And I don't want to break the link to the database since I update the results periodically. ...

How to Use Data Migration Pack
Hi All, i downloaded the DMF from Microsoft.com, i read the documents but i couldn't understand that, My exact Requirement is, i have to import 18000 Records to Product Catalog, what i have to do now, please give me guidelines to import Records, Thanks Thangadurai On Sep 8, 7:04 am, mukathangadu...@gmail.com wrote: > Hi All, > i downloaded the DMF from Microsoft.com, > i read the documents but i couldn't understand that, > My exact Requirement is, i have to import 18000 Records to Product > Catalog, > what i have to do now, please give me guidelines to import Reco...

Data vs. forms & Unload/reload questions
A .mdb file seems to encompass both the database and all the 'code' (forms, modules, and logic) used to CRUD the data in the tables. 1) Is there any way to separate the database from the code into separate files, eg. the db is mydata.mdb and the 'code is in a separate file, eg. code.mdb? 2) Say you have v1.0 of an app and the tables and attributes are populated with data. You then go and build v2.0 of the app, adding tables and columns to what already existed in v1.0, and perhaps moving some of the v1.0 attributes into different tables. What is the recommended way to unload/reloa...

cell range not changing when refreshing linked data- sumproduct fo
The cell range is not updating correctly to reflect the last row in the data sheet that is being refreshed. 5878 is the correct number of rows and 5824 is not. I can find and replace in my formulas to correct the problem, but it does it each time the data is refreshed. Any suggestions as to why? =(SUMPRODUCT((Expense!$B$2:$B$5878=$C$2)*(Expense!$C$2:$C$5878=$C$3)*(Expense!$W$2:$W$5824=$A6)*(Expense!$M$2:$M$5878))) I gave you the INDIRECT option y'day, but received no feedback from you in that thread. Looks like you're more interested in knowing why? One simple hunc...

Ignore a Blank Data Series in the Legend
Hi, I'm creating Pie Charts based on data from 10 data series fields. Some of the data fields are blank, and so I've managed to create data labels that only show on the Pie Chart if there is a value (otherwise the Chart was too hard to read due to lots of blank labels). However, if I show the Legend, the blank data series field still appear, so I have say, 4 labels with the correct data series names but 6 which are blank. Can I make the Legend ignore blank fields, and just show the 4 that have values? Thanks You can manually remove individual legend entries. Click on the lege...

Extract unique data
I am using excel 2003 I have a file like below Rate month GBP 1.46 FEB 2000 1.47 MAR 3000 1.47 APR 3500 1.48 MAY 1000 I want to create summary Rate FEB MAR APR MAY 1.46 2000 1.47 3000 3500 1.48 1000 I can use sumif function to return the total, however, how can I insert only the unique rate in the first column automatically. Thanks a lot ! eva cheng Hi, I suppose information is in sheet 1 columns A,B...

Data validation doesn't work if contents is added by pasting
Hi I've data validation set so that only the number 1 can be entered, but even this cannot be entered if an adjacent cell is a certain value. this works for direct entry, but not if the value is pasted in or added using the "drag handle". Is there any way to stop this happening as I just know somewones going to try and fill all the cells by draging the values down. Regards Jeff Jeff, You could disable "Allow cell drag and drop" in Tools | Options or... copy the following code, right click the sheet tab and choose 'view code'. Paste the code into the modu...

Dropdown lists thru Validation not friendly to Manual Text Entries
Using Excel 2000. I created a (long) dropdown list using the Validation feature. When users go to enter data, some prefer to never touch their mouse. And scrolling through this long list is cumbersome for everyone. Is there a way to either autocomplete an entry based on the items in the list? Or to have the dropdown box "intelligently" scroll when a user types a few characters (For instance, drop to the "S's" when he or she keys in an 'S'?) thanks! (sorry if this was addressed in other posts, I really have tried searching) --- Message posted from http:...

Moving a line chart data point revises data table value in Excel '
I recall a capability I used with Excel years ago that enabled me to create a line chart from a data table, then move the plot points on the chart to "smooth" the line, and the source data chart value would change with the corresponding new plotted point. I'd like to use that capability with Excel 2007 but can't find to activate it. Any power users know how? Thanks! Ed Ed, Microsoft decided that users didn't need that feature. More than likely, they were tired of support calls asking why the values changed when one dragged a data point...... No really, they took i...

Day Validation code
Hello, I am looking for some help for my data entry form. In the form there is a combo box, which is used to enter date ("cboLongDate"). The date format is "Wednesday, June 13, 2007" . I need to confirm the day is Wednesday or not before the user click on add data or close form. The important thing is day (ie "Wednesday") not the date as Wednesday comes every week. If the selected day is not "wednesday" a message box should pop up saying the day selected is incorrect while closing or adding data so that user can get a chance to correct it. Thanks in adv...

Protect Data, but allow sorting
Hello, In Excel 2003 is it possible to protect the data in a sheet, but to allow the data to be sorted? In the Protect Sheet command, it indicates that this facility exists, but when I try to do a sort it won't do it until the sheet is unprotected. Pete Make sure that there's an empty column between the range to be sorted and any "adjacent" data. If you have a locked cell butting up to the range to be sorted, excel yells! Pete Walburn wrote: > > Hello, > > In Excel 2003 is it possible to protect the data in a sheet, but to allow > the data to be so...

comparing data #2
I was wondering is there a way to compare data from 2 columns in a side by side comparison. I have a large list of numbers and what I want to do is copy another list of numbers into the spreadsheet. But I want the numbers in cell A1 and B1 to be equal if they are is not a match then leave cell b1 empty. I want that to continue all the way through the entire worksheet. Maurice, Do you want to end up with 2 separate columns, or are you wanting to merge the 2 lists into 1 list, without duplications? Dave I want 2 side by side columns. With the numbers that are equal right next to each othe...

External Data from Secured Access Application...
I have a secured access application with a query that I want to use as the source for an excel chart. When I attempt to use anything out of this Access application (even a raw table), the MS Query window won't expand to show the field(colums). Can anyone help me get this working??? Kathy ...