If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Hi
have a look at the VLOOKUP function. see: http://www.contextures.com/xlFunctions02.html -- Regards Frank Kabel Frankfurt, Germany "ecarnes0723" schrieb im Newsbeitrag ... 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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I populate a field in a form based on a value in a previou. | Jim B. | Using Forms | 1 | September 24th, 2004 10:21 PM |
excel 97 vba - need to associate values with drop down list choice | chief | General Discussion | 15 | July 23rd, 2004 11:52 PM |
Field list greyed out when trying to add an option group | Tony | Using Forms | 1 | July 1st, 2004 03:40 AM |
NUMBERING the pages | Bob | New Users | 7 | June 14th, 2004 12:20 AM |
Sorting based on a drop down list | Randall Roberts | Worksheet Functions | 1 | November 11th, 2003 10:59 PM |