Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex data referencing - too many for nested IF's
I am creating a sheet that lists various assembly components (part number &
part description - in two separate columns), and I would like to be able to enter in the part number, and have Excel automatically fill in the part description based on that part number. The list I am creating uses many parts over and over again for several assemblies, so it would save valuable time if I could streamline this process. Is there a way I could have some sort of master list, where all part numbers are listed with their associated descriptions, and use that as a reference? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex data referencing - too many for nested IF's
Try this:
Put a new worksheet in your workbook, then: A1: PartNum B1: PartDesc A2: PartNum_A (or whatever products you have) B2: Description of PartNum_A Continue filling in the list When done... Select from A2 through the last item in Col_B InsertNameDefine Name in workbook: LU_PartInfo Refers to: (your already selected list) Click the [OK] button Next, switch to your input sheet Select the PartNum input cells DataValidation Allow: List Source: =OFFSET(LU_PartInfo,0,0,,1) Click the [OK] button Select the PartDesc cells (I'll assume they begin in cell B2) B2: =IF(ISBLANK(A2),"",VLOOKUP(A2,LU_PartInfo,2,0)) Copy that formula down as far as you need. Results: The PartNum input cells will only allow valid products. The Part Description cells will lookup each product in the LU_Partinfo list and return the corresponding description. Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "djDaemon" wrote: I am creating a sheet that lists various assembly components (part number & part description - in two separate columns), and I would like to be able to enter in the part number, and have Excel automatically fill in the part description based on that part number. The list I am creating uses many parts over and over again for several assemblies, so it would save valuable time if I could streamline this process. Is there a way I could have some sort of master list, where all part numbers are listed with their associated descriptions, and use that as a reference? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex data referencing - too many for nested IF's
That's close. Eventually, perhaps the validation of part numbers could be a
reality, but until the list is completely populated I would still need to enter in additional non-validated numbers. Of course, I could enter the parts into the reference list beforehand, having left room for future entries in that list. "Ron Coderre" wrote: Try this: Put a new worksheet in your workbook, then: A1: PartNum B1: PartDesc A2: PartNum_A (or whatever products you have) B2: Description of PartNum_A Continue filling in the list When done... Select from A2 through the last item in Col_B InsertNameDefine Name in workbook: LU_PartInfo Refers to: (your already selected list) Click the [OK] button Next, switch to your input sheet Select the PartNum input cells DataValidation Allow: List Source: =OFFSET(LU_PartInfo,0,0,,1) Click the [OK] button Select the PartDesc cells (I'll assume they begin in cell B2) B2: =IF(ISBLANK(A2),"",VLOOKUP(A2,LU_PartInfo,2,0)) Copy that formula down as far as you need. Results: The PartNum input cells will only allow valid products. The Part Description cells will lookup each product in the LU_Partinfo list and return the corresponding description. Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "djDaemon" wrote: I am creating a sheet that lists various assembly components (part number & part description - in two separate columns), and I would like to be able to enter in the part number, and have Excel automatically fill in the part description based on that part number. The list I am creating uses many parts over and over again for several assemblies, so it would save valuable time if I could streamline this process. Is there a way I could have some sort of master list, where all part numbers are listed with their associated descriptions, and use that as a reference? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex data referencing - too many for nested IF's
"djDaemon" wrote: That's close. Eventually, perhaps the validation of part numbers could be a reality, but until the list is completely populated I would still need to enter in additional non-validated numbers. Of course, I could enter the parts into the reference list beforehand, having left room for future entries in that list. "Ron Coderre" wrote: Try this: Put a new worksheet in your workbook, then: A1: PartNum B1: PartDesc A2: PartNum_A (or whatever products you have) B2: Description of PartNum_A Continue filling in the list When done... Select from A2 through the last item in Col_B InsertNameDefine Name in workbook: LU_PartInfo Refers to: (your already selected list) Click the [OK] button Next, switch to your input sheet Select the PartNum input cells DataValidation Allow: List Source: =OFFSET(LU_PartInfo,0,0,,1) Click the [OK] button Select the PartDesc cells (I'll assume they begin in cell B2) B2: =IF(ISBLANK(A2),"",VLOOKUP(A2,LU_PartInfo,2,0)) Copy that formula down as far as you need. Results: The PartNum input cells will only allow valid products. The Part Description cells will lookup each product in the LU_Partinfo list and return the corresponding description. Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "djDaemon" wrote: I am creating a sheet that lists various assembly components (part number & part description - in two separate columns), and I would like to be able to enter in the part number, and have Excel automatically fill in the part description based on that part number. The list I am creating uses many parts over and over again for several assemblies, so it would save valuable time if I could streamline this process. Is there a way I could have some sort of master list, where all part numbers are listed with their associated descriptions, and use that as a reference? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a new line in spreadsheet | Excel Discussion (Misc queries) | |||
Importing Data | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Charts not recognizing source data if original linked data is changed. | Charts and Charting in Excel | |||
referencing a sheet named in a cell then using data from that sheet | Excel Worksheet Functions |