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
497 Views

Similar Articles

[PageSpeed] 34

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:

forwarding distribution lists
can someone forward you an outlook distribution list that you can then import into your own outlook instance? Outlook 2003 SP2 -- windows xp pro. The sender can just drag the list from their Contacts folder into the email they're sending to you. Then you just drag it into your Contacts. -Peter "thersitz" <thersitz@gmail.com> wrote in message news:%23Q5fOEJRHHA.2076@TK2MSFTNGP05.phx.gbl... > can someone forward you an outlook distribution list that you can then > import into your own outlook instance? > > Outlook 2003 SP2 -- windows xp pro. > ...

Remote another objects in a another applications
Dear All... Is it possible to control or to remote another objects in another msaccess application from the current msacces applications, that opens in the same time. I had a form to edit some records, and I want to display the results in the other form in the other applications and in the same time I could also control/remote that application like open or close forms or other objects. Currently I'm using dual monitors that attached to one computer but located in different rooms. Is it make any sense?... I've already knew how to remote one object from another in one same ap...

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...

sql tabel listing
We have let our maintainance agreement lapse and now I need to create some reports. Is there anywhere I can get a listing of tables and the fields they contain? Larry check this KB article http://tinyurl.com/nao8m you must have access to customer source Greetings "Larry Jenuwine" wrote: > We have let our maintainance agreement lapse and now I need to create some > reports. Is there anywhere I can get a listing of tables and the fields they > contain? > > Larry ...

continued: multiple selections in a list-box
There was a thread started on 9/15 about this (see below) where a list box with multiple selections would be dropped into a text field separated by by commas (or whatever you want). The corrolary to this then is: now that I've got a text / memo item filled with multiple values, how do I extract that back to the list-box if the form is opened up later to that record. Secondarily, is there a way to pull each of the values from the list box and have it populate a different table that has the record number and choice. For example, the main form (tbl_main) has you choose three or more...

outlook safe distribution list
I would like to set up a distribution list that uses the bcc field for the members of the list to protect the identities of the persons on the list but still takes advantage of the personalized to: field for each member. Help would be appreciated. Thank you. You can't do this by using the BCC field then. Use the Mail Merge option from within Word instead. -- Roady [MVP] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD slipstreamed with Service Pack 3 ----- <mcgongee@yahoo.com> wrote in message news:1188501c441cc$82119260$a601280a@phx.gbl...

Sorting file list by date/time...
Hello, does anybody have an idea how to program a sorting function given a directory with files, which should red to an array which is sorted by date and time, but contains only the filenames. Is there a MFC function or a shell function of the OS which could ease the job instead of doing it manually? Regards, Diana On Mon, 16 Apr 2007 16:39:50 +0200, "Diana Bulthaupt" <Diana.Bulthaupt(at)web.de> wrote: >does anybody have an idea how to program a sorting function given a >directory with files, which should red to an array which is sorted by date >an...

Color folder list
Hello there, Does anybody know how to change the color of the folders in de folder list. Inbox etc... (outlook 2002) Thanks, Greg Not possible. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Greg" <anonymous@discussions.microsoft.com> wrote in message news:482d01c48049$2966d6f0$a301280a@phx.gbl... > Hello there, > > Does anybody know how to change the color of the folders > in de folder list. > Inbox etc... > > (outlook 2...

Incorrect Field Names Appear in PivotTable Field Dialog Box
I have found that items deleted from my source, standard worksheet, are still appearing in the Pivot Table Drop down list. I have applied the sollution in MS202232 but it did not clearup the outdated data. I have refreashed the pivot table before and after runing the MACRO "Delete_Fields" Not sure what else can be done, I continue to search groups and MS. ******** This is the code for the MACRO based MS202232: Sub Delete_Fields() On Error Resume Next For Each pvtfield In Worksheets(PivotTables).PivotTables(PivotTable1).PivotFields For Each pvtitem In pvtfield.PivotItem...

Return a value when Date x is between Date y and z
Hi, I have a table something like this: A B C 1 From To Result 2 01/07/2009 01/07/2010 0-1 3 01/07/2010 01/07/2011 1-2 4 01/07/2011 01/07/2012 2-3 5 01/07/2012 01/07/2013 3-4 In another column (X) I have a list of Dates and I want return the "result" from the row where the date lies between. So for example in X3 I have 16/10/2011 and I can see this date is between the 2 dates in row 4 in the table above so I want to return "2-3" I've tried an array formula ...

How to 1. Jump to a specific page (in a massive document) based on a search then 2. Copy and paste an string of characters a few lines above it.
BACKGROUND/CONTEXT I am working from a very large word document, approximately 1000 pages long. Each page has one 'article' on it. I am currently doing something manually, which I would love to do using a macro. I am populating a table (which happens to be in an Excel spreadsheet) One column is the Article number 'xxxxx'. One of the other columns is a 'Topic' which is typically three alphanumeric characters (like AA1, AD4, BB4 DO6 etc.) PROBLEM/TASK This is what i have to do Step 1 - Jump to the page with the article number. Achieved by p...

Restore deleted distribution list
I accidently deleted an entire distribution list in Outlook 2007. I wanted to delete a single contact from the list and clicked on the "X" and my entire list was deleted with out prompting me if I wanted to delete it or a single contact from it. I have checked my deleted items under the mail tab as well as my recyle bin and can not find the list. I had over 150 people on the list. How do I restore the entire list to my contacts? If I can not restore the entire distribution list is there a way to recreate it by importing the contact from a prior email I sent to the ...

forms not listed in database contents, yet can add data to them
Hi - there are no forms listed when I open up the database in design view. All the tables, queries, and macros are there. When I open up the database to add data, all the forms are there and can be used. The data is stored. So, it seems that the forms are still there, but just cannot be seen in design view. The database, when I open it up in design view, says Access 2000 file object even though I am using Access 2003 - could that be the problem? Thanks! You cannot open an entire database in Design View. Try opening it normally, selecting a form, and choosing Design View from the V...

Another question, regard enum { IDD = DIALOGID };
Hope it's okay if I ask another question, I noticed that my dialog lacked an IDD-enum. When you create a new dialog project, say, you get something like the following in the public section of the dialog class header: enum { IDD = IDD_ASIMPLETEST_DIALOG }; I noticed I didn't have one in this old project so I added one but it won't compile because the compiler doesn't see the identifier I'm using, which is due to the fact that Resource.h isn't included in the dialog class header file. But the thing is, it isn't included in my test project either but it compiles...

Global List Problem on Exch 5.5
Hi , I am trying to resolve this, a user has joined the London branch, she is listed in the Cambs Global address book as being in Bristol and Cambs despite not having worked at the Bristol branch for over a year, on contacting the Bristol branch I find that the user no longer appears in the Bristol Global list, the user is only listed in the Cambs Global address book , confusing! does this sound like a replication issue. Any advice gratefully received. regards Kev On Mon, 11 Oct 2004 00:25:53 -0700, <anonymous@discussions.microsoft.com> wrote: >Hi , I am...

Enter month, get the first date
Hi, I want to be able to select the month from the drop down list and once the month is selected, I want the first date cell to change itself to the first date of the month selected. For rest of the date cells I have just used the formula "First date cell + 1" any help will be appreciated!! Thanks a lot in advance. Check your other post. Gaurav wrote: > > Hi, > > I want to be able to select the month from the drop down list and once the > month is selected, I want the first date cell to change itself to the first > date of the month ...

inserting text form field in a text box
I'm designing a form that has several text boxes I want to be able to insert a text form field that I can type in different names in when needed, while keeping the rest of the text constant. Every time I go to insert form field from Legacy it is not available. Anybody have a solution. Your subject line has the missing clue. There is no way to insert a legacy form field (text or any other kind) in a text box, footnote, endnote, or header/footer. You can remove the text box and put the field into ordinary text, or you can use a text content control in a text box or elsewhe...

SeriesCollection Values Property Oddity
This has long perplexed me. Thought I would ask someone. The Values property of a series apparently returns an array of all the point values in the series. Howver, one apparently can't extract an element from this array. The following macros demo the issue. Looking for enlightenment: 'This works Sub Test1() Dim i As Long With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1) For i = 1 To .Points.Count MsgBox Application.Index(.Values, i) Next End With End Sub 'This also works Sub Test2() Dim arr As Variant Dim i As Long With ActiveSheet.ChartObjects(1).Char...

Contact to Address Relationship adding new Address fields
The Contact Entity has a number of field attributes attached to it from the Address table like Address1_city. With CRM 3.0 can I add a new field to the Address table and get it to link to the contact like Address1_City? If so how would I do that? I don't want to have to add a field to the contact table and then map it to the address table because that will mean having two copies of the same data and having them keep each other in synch in an onchange event, if I can. Can I do what I want or do I have to take the second approach? Mike You can do this in a completely unsupported w...

Want to hack the recently used file list max entries
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Would anyone care to offer a hack method to get the recently used file list to show more than 9 entries? No use telling me it can't be changed. I am asking for the &quot;Yes&quot; people to answer. OK, yes... The first thing you need to do is contact Microsoft & ask them for the source code. Seriously, there is no user option other than the setting provided in Preferences. If you want to see a longer list use the 'Recent' page of Project Gallery from the file menu. On its Customize page ...

Drop Down Lists and Error Alerts
I have created a drop down list and want to put in an error alert, so that people cannot enter text into the fields (and can therefore only use the data in the drop down boxes). I have gone to the error alert tab in data validation and ticked the show error alert after invalid data is entered and the selected stop from the drop down box (and entered a short message). However, when I have tested the fields it is still allowing me to enter text and no warning message is coming up. Any ideas on what else I might need to do to make it work. Thanks. What data validation criteria have you...

Outlook 2003 EAS server-based Archive solution Questions
At work they installed EAS on selected PCs including, I believe, my PC. All of my older email messages in the Inbox have a symbol that looks likke an external drive, CD disk and an envelope. When I open these messages the original attachment isn't there just the attachment titles. What do I need to do to get to the attachments? On my PC I don't see the EAS Icon in the System Tray. Does EAS need to be loaded on my PC before I can open EAS archived attachments? Thanks, Tom What is EAS???? --=20 Milly Staples [MVP - Outlook] Post all replies to the group to keep the di...

Retrieve information from another field / entity
Hello. I've searched around and found some code for me to be able to get the value from two fields in the Account Entity to two fields in the Opportunity Entity. This would happened on the OnChange event of my "client" / "account" field. This field is the customerid field and is type "customer". It seems the code is doing what it's supposed to do until the end...where it's supposed to populate the two fields in the opportunity. I get the following error: "There was an error with this field's customzied event. Field: customerid ...

Not Print Zero values
Howdy, I need to do one of two things: 1) My zero values are hidden but they span down 300 cloumns. Is there away to hide these from printing? 2) The zero values above are a simple currency total between tw fields. I have filled the incrementing formulas down about 300 rows. I there a way to fill in the next incrementing formula [(b2)*(d2)] whe the calulation is made? I want to keep from having 300 growin formulas, I'd rather try to have them incrimentaly auto fill so m print outs wont have 20 extra blank pages. many thanks -- RubberSou ----------------------------------------------...

Vanishing E-mails, They drop off of the face of the earth.
I have noticed a new and intresting behavior in a client's inbox since it was "re-built". E-mails will Arrive and appear in the Inbox of outlook, then suddenly vanish. I am running Exchange 5.5 and the version of outlook is 2000. The client's inbox had been distroyed by an imported .pst that was unknowingly way too big for Exchange to handle. When inported the Inbox was frozen, and in short had to be deleted. The inbox was recreated with the same name and addresses. The problem is not consistent, only a few e-mails will "stick", most just dissapear. I would ...