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?

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?

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?

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?