enter value based on drop down list of another field?

I have an excel 2003 spreadsheet with 3 worksheets. In sheet 1, I created a 
drop down list in column A  based on values in sheet 3, column A (item 
number).  Thisd data comes from sheet 3, where I have column A (Item #'s), 
column B (Item desc)an Column C (price)

In column B of sheet 1, I have Item Descriptions and in column C I have 
price.  I want this to automatically fill in based on what I enter in the 
column A sheet 1 and it should get the corresponding values from sheet 3 
column B and C. I can make the drop down list in sheet 1 column B and C, but 
some of the descriptions need the Item Number to make sense.  I want to 
select a value in sheet 1 column A, and have the corresponding item 
description and price from sheet 3 column B and C filled into sheet 1 column 
A and B.
Thanks, Ed 
ecarnes0723@carnesgroup.com
0
10/14/2004 5:05:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
523 Views

Similar Articles

[PageSpeed] 15

Hi
have a look at the VLOOKUP function. see:
http://www.contextures.com/xlFunctions02.html

--
Regards
Frank Kabel
Frankfurt, Germany

"ecarnes0723" <ecarnes0723@discussions.microsoft.com> schrieb im
Newsbeitrag news:E13446B9-A6FB-4481-9BAD-9721CD6BF28D@microsoft.com...
> I have an excel 2003 spreadsheet with 3 worksheets. In sheet 1, I
created a
> drop down list in column A  based on values in sheet 3, column A
(item
> number).  Thisd data comes from sheet 3, where I have column A (Item
#'s),
> column B (Item desc)an Column C (price)
>
> In column B of sheet 1, I have Item Descriptions and in column C I
have
> price.  I want this to automatically fill in based on what I enter in
the
> column A sheet 1 and it should get the corresponding values from
sheet 3
> column B and C. I can make the drop down list in sheet 1 column B and
C, but
> some of the descriptions need the Item Number to make sense.  I want
to
> select a value in sheet 1 column A, and have the corresponding item
> description and price from sheet 3 column B and C filled into sheet 1
column
> A and B.
> Thanks, Ed
> ecarnes0723@carnesgroup.com

0
frank.kabel (11126)
10/14/2004 5:16:49 PM
I leave column B empty, and size to fit the width of the dropdown arrow (this 
way the arrow doesn't obscure data in the next column).  

Try this formula in column C (copy down as far as needed and over to col. D):

=IF(ISERROR(VLOOKUP($A1,yourdatarange,2,0)),"No 
Selection",VLOOKUP($A1,yourdatarange,2,0))

The ISERROR prevents #NA being returned in columns with no entry.  You could 
remove the text(No Selection) and just leave the double quotes ("" with no 
space) to return a blank cell.

It is easier if you name your reference range, but you could enter the 
parameters (sheetname!upperleftcell:lowerrightcell).

HTH

Bruce

"ecarnes0723" wrote:

> I have an excel 2003 spreadsheet with 3 worksheets. In sheet 1, I created a 
> drop down list in column A  based on values in sheet 3, column A (item 
> number).  Thisd data comes from sheet 3, where I have column A (Item #'s), 
> column B (Item desc)an Column C (price)
> 
> In column B of sheet 1, I have Item Descriptions and in column C I have 
> price.  I want this to automatically fill in based on what I enter in the 
> column A sheet 1 and it should get the corresponding values from sheet 3 
> column B and C. I can make the drop down list in sheet 1 column B and C, but 
> some of the descriptions need the Item Number to make sense.  I want to 
> select a value in sheet 1 column A, and have the corresponding item 
> description and price from sheet 3 column B and C filled into sheet 1 column 
> A and B.
> Thanks, Ed 
> ecarnes0723@carnesgroup.com
0
Utf
10/15/2004 2:57:11 PM
Reply:

Similar Artilces:

How can I drop the year from mm/dd/yyyy data?
I have a large data set consisting of values from multiple years that I would like to analyze by time of year. ie: I want to look at the month/day for each value while ignoring the year. Specifically I want to end up with a graph showing time of year (in months) across the x-axis and showing all my data points as if they were from this one hypothetical year. Seems like it should be simple but I can't seem to crack it. Any help is greatly appreciated. Hi, Try Format > Cells > Custom: dd/mm -Mark >-----Original Message----- >I have a large data set consisting of valu...

History/Activity-NTEXT Field
Ok, here's the deal, and lets see if it get broken here when I past it in, haha. 1. Try to view cirtain customer's history and get sql error (soap exception, you know the one that really doesn't tell you anything). 2. Look at customer's "History" by using advanced find to find all the activities. 3. Notice that one of the activities has it's 5000 character NTEXT Field filled with what appears to be a "copy/paste" from an outlook e-mail, to which I can open just fine. 4. out of sheer desperation of trying to figure out what the problem is, I wen...

Import Excel Data from another workbook or file
Is there a way to import or export Excel Data between 2 different workbooks? E.G. Import from "File A" 'Sheet1' Column A, Row2 to "File B" 'Sheet1' Column A, Row 2. What I'd like to do ultimately is import data from 5 excel files into one. tamato43 wrote: || Is there a way to import or export Excel Data between 2 different || workbooks? || || E.G. Import from "File A" 'Sheet1' Column A, Row2 to "File B" || 'Sheet1' Column A, Row 2. || || What I'd like to do ultimately is import data from 5 excel files || in...

empty pivot table fields
How can I force a pivot table to, for example, say that there were no accidents in week 2. At present it says 3 in week 1 then jumps to 2 in week 3. I want week 2 =0 for charting purposes. -thanks Right-click on the Weeks field button. Select Field Settings Add a check mark to 'Show items with no data' Click OK bobf wrote: > How can I force a pivot table to, for example, say that there were no > accidents in week 2. At present it says 3 in week 1 then jumps to 2 in week > 3. I want week 2 =0 for charting purposes. -thanks -- Debra Dalgleish Excel FAQ, Tips & Book...

Count data or list
I am trying to count a data group and return the quanity of unique entries: It might look like this: The data would be in cols & rows 2 green widgets 25.5 inches long 3 green widgets 28.3 inches long 1 brown widgets 52.1 inches long 2 green widgets 25.5 inches long 1 brown widgets 52.1 inches long the result would be: 4 green widgets 25.5 inches long 3 green widgets 28.3 inches long 2 brown widgets 52.1 inches long Any simple straight forward way to do this??? Thanks, Spydor -- spydor ----------------------------------------------------...

Display lists in an arbitrary number of columns
I have written and posted the following articles which will prove useful = to some: Display Lists in Columns Horizontally Using One Cell per Column http://www.braintrove.com/default.aspx?p=3D3&s=3D1&i=3D34 Display Lists in Columns Horizontally Using Individual Cells http://www.braintrove.com/default.aspx?p=3D3&s=3D1&i=3D33 Display Lists in Columns Vertically Using One Cells per Column http://www.braintrove.com/default.aspx?p=3D3&s=3D1&i=3D32 Display Lists in Columns Vertically Using Individual Cells http://www.braintrove.com/default.aspx?p=3D3&s=3D1&i=3D31 ...

Delete jpg file based on Access field contents
Hello, What would be the best way to have access delete a bunch of jpg's? I have two tables in mdb format, the main table has 36000 records. When I am done with a record, I move it to a Deleted Record table (using a query and a macro). There are about 1300 of the deleted records that have an associated jpg file. The file size is around 100k, so I have around 130 meg of files cluttering up my laptop. I will never look at the jpg's again. I know it's not a lot of space, but why not have some code get rid of them? So, should I use a button on a regularly accessed Fo...

Determine a cell's width and enter it in the cell
I have several workbooks with multiple sheets that I'd like to have the same various column widths on each sheet. I get it all set up and then one column will need to be wider. Now I have to go to a cell in each column, check column width, and enter it in each cell. I then have a sub that reads the value in a cell and sets the width to that value so that I can copy the row to each other sheet to resize them. Is there a way to automatically read a cell's width and enter that value in the cell? Thanks for any suggestions. As an alternative: you could "group" all the...

Not in the list event
Hi, I have combo box the takes data from Customerstbl. If a user type into the combo box i would like to propmt him with massage and give hm the option to add it to the table. What is the correct code in the Not in the list event? Thank you , Tom Tom Take a look at http://www.cardaconsultants.com/en/msaccess.php?lang=en&id=0000000011#notinlist for an example, this is something that you can costumize depending on the situation. If you search this forum, or google not in list even you'll get tons more examples. -- Hope this helps, Daniel P "Tom" <part...

Converting an Excel List into Access
I am unable to import a specific excel document into Access. I have done countless other imports and never encountered an error. What are some possible reasons for being unable to import the file? Yes all that shows up is: "An error occured while trying to import(directory path here). The file was not imported" >-----Original Message----- >What happens when you try to import it? Is there an error message? > >Dano wrote: >> I am unable to import a specific excel document into >> Access. I have done countless other imports and never >> encountered...

Calculate fees based on the percentage given
Based on the example and table in the attachment, I want to calculat Total Cost based on a specific percentage. At first, I need to calculate based on Class 2 minus Class1. The Total Cost is calculated after a fees is entered. I want the steps of calculation (shaded in blue in my worksheet) to be displayed in the worksheet too. How can i do that? Any and all help are appreciated! Thanks, p/s Please refer to the attachment +---------------------------------------------------------------- | Attachment filename: book1.xls |Download attachmen...

Item Allocation Inquiry
Dynamics enables you to allocate inventory on the Parts Information window of Depot Management (part of Field Service). After I have included parts in the Parts Information window, when I then go to the Item Quantities Maintenance screen, I can see that these parts have been added to the Allocated quantities. When I double-click on the Allocated label on the Item Quantities Maintenance window, the Item Allocation Inquiry window opens. However, I do not see the allocations made by Depot Management, I assume, because it is has not been selected in the Module Restrictions box . It has not ...

Using Web Services To Add Leads to Marketing List
Is it possible to use web services to add leads/contacts to marketing list? I have a sales contact form that creates a lead, and I want to give those leads the option to signup for a mailing list we have. Does anyone know if this is possible? If so, know any examples? Thanks, Tareef Hi Tareef, The messages to handle list membership are AddMemberList and RemoveMemberList. They are described in the SDK documentation along with some sample code. -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup D...

compare values
I have a sheet with many rows of numbers. The numbers in each column are supposed to be identical. Is there some way to verify that all the numbers are identical without manually scanning them? Ivor Hi Ivor if by identical you mean A1=B1 then in C1 simply type =IF(A1= B1,"","ERROR") and fill down (double click on + at bottom right corner of the cell) - you can then check out the "error" ones if, however, you mean the numbers are somewhere in the first column and somewhere in the second column but not necessarily next to each other, use the array formula...

finding the second largest number in a list
Is there a way of finding the "second largest" number from a list of 10 numbers ? I know MAX finds the biggest number but unsure if numbers of a particular rank in a list can be identified. Thanks usef <large> function =large(array,k) i.e kth largestumber see help for <large> similarly <small> bobf <anonymous@discussions.microsoft.com> wrote in message news:20b601c51429$50f008d0$a501280a@phx.gbl... > Is there a way of finding the "second largest" number from > a list of 10 numbers ? I know MAX finds the biggest > number but unsure i...

stacked fields 'can grow' but overlap in report
I have four fields stacked one above the other on a report. They're all marked as 'Can Grow', and sometimes they do. In one case, a multi-line data field pushes the field below out of the way. That's good. But in another case, a multi-line field overlaps with the field below. In every case the fields are standard text boxes. However, I noticed there is a difference in the associated label. The labels that push down with their fields do not have any events available for attaching code. The other labels have events, although I'm not using any events. ...

auto copy and paste to another workbook by using specific titles
I am trying to get information from a row that contains a specific title to be transferred to another workbook automatically. I also need to know how to do this with the cells not matching up. Hello: Your question is vague. If you mean that when someone opens the spreadsheet, the contents of a row with a certain title is copied to another spreadsheet, then you can use the Auto_Open() event to search for the row, open the target spreadsheet, insert it, and then close the spreadsheet, saving the contents. Again, I may not be addressing your question, but again, it very gener...

compare data in two lists to find matching entries #4
I have a spreadsheet with two colums. Each colum contains an identification reference e.g. Colum A Colum B abc123456 nmg4568987 ahsj45236 abc123456 ikl7856489 jkh7854566589 I want excel to check if the items in colums b are also in colum a and insert either "Match found" or "No match found" in colum c. For example abc123456 nmg4568987 No match found ahsj45236 abc123456 Match found ikl7856489 jkh7854566589 No match found Please help. I've tried everything I could think of. Nothing seems to work. =IF(COUNTIF($A$1:$A$3,B1)>0,"M...

Tables entering data and sorting
Is there a way to setup tables in Word so when you hit enter it goes to the next cell instead of adding a new line? Just like Excel does? Is there a way to sort only one column in the table? I select one column and it sorts the entire table? Thanks in advance. Please see my reply to your duplicate question in word.tables. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "falcios" <falcios@discussions.microsoft.com> wrote in message news:3E01E4A7-4C55-4A93-BF91-26DB8460A15F@microsoft.com... > I...

Mailing list digest problem with Outlook 2003
With Outlook XP, mailing list daily digests that came into my mailbox were a long text mail containing all the messages posted to the list for that day. After upgrading from Outlook XP to Outlook 2003, instead of getting a long text message containing all the separate postings to the mailing list, each posting is an attachment to the message and has to be opened separately, which is most inconvenient. How can I change the behaviour back to the way it was previously? I''ve poked around in the various options and can't find anything relevant. It's definitely Outlook 2003 causing...

%s in XL entered as ".5" OR "50" reads as 50% if format is %age.
I have a spreadsheet I work with every day and I use fractional percentages, between 0-1%. If the field is formatted as Percentage, and I type in ".5", it reads it as 50%, which it would be if it weren't a percentage. But it is. It's a half percent. By typing in "50", I also get 50%, which doesn't make sense. If I type in "5" I get 5%, if I type in ".5", I should get .5%. Is there a way to make this happen, or is this just a glitch in Excel? Please help me figure this out. Thanks. abuzzmaster@yahoo.com Try this, Buzz: http://www.office...

You made changes to another copy of this object...
Hi all, Somewhere out of nothing when I open a task the following message appears in a grey bar on top of the task: "You made changes to another copy of this object. This is the most recent version. Click here to see the other versions" Why does this happen? It is a reoccurring task, and the task from the past which was markt completed suddenly is overdue.. Is there an explanation for this?? Regards, M. ...

Would anybody help me to recover the "Exception List Rule" on O2000?
I have removed it and I don't know how to recover it. Thank you. Have you enabled the Junk Mail Rule "Javier Hernando" <jhernand@hbas.osakidetza.net> wrote in message news:%23eCyBia2DHA.208@TK2MSFTNGP12.phx.gbl... > I have removed it and I don't know how to recover it. > > Thank you. > > ...

Handling 'Enter' button for EditControl
Hey guys, I am using an EditControl box. It is only a single line text box and I was wondering how I could go about performing some action when the user hits the Enter key? I tried playing around with the various possible events, but it seems that none of the events actually do what I would like them to do :( Any help would be greatly appreciated... Thanks If the edit control is on a dialog (assuming it is) you could just handle the OnOK() function to catch the enter key and OnCancel() or OnClose() to catch the escape and red x events. Unless you are trying to use the enter key to go...

Remaining Predecessors Custom Field
Hi, I am working in a large Project document (1000+ tasks) with a lot of dependencies. My goal is to create a formula in a custom field that shows only remaining/uncompleted predecessors, but I am having trouble getting something that works. Would any of you be able to point me in the right direction or provide me with a solution? Thank you, Jason -- jdd23 ------------------------------------------------------------------------ jdd23's Profile: http://forums.techarena.in/members/171363.htm View this thread: http://forums.techarena.in/microsoft-project/1290250.htm ...