Forum Discussion
stephurso
Jan 25, 2022Copper Contributor
Validate Part Number in Cell
Hello, I have a spreadsheet with cells B11 through B65 with a 4 digit part number. Example, 4112. If a user enters a part number in cells B72 through B150 but that part number is not a valid par...
- Jan 25, 2022
=IFERROR(VLOOKUP(B72,$B$11:$C$65,2,FALSE),"Invalid Part")
This works in my spreadsheet. Unfortunately from your initial post i didn't understand that there are part descriptions in range C11:C65 which you want to retrieve.
stephurso
Jan 25, 2022Copper Contributor
This works however when its a valid part number it does not pull in the part number description in the corresponding cells. Cells C11 through C65. It just pulls in the part number now.
If I type in part number 4125 in cell B72 and hit enter, if its a valid part it will display the full part number description that is listed in cell C63 if not, "Invalid Part".
If I type in part number 4125 in cell B72 and hit enter, if its a valid part it will display the full part number description that is listed in cell C63 if not, "Invalid Part".
OliverScheurich
Jan 25, 2022Gold Contributor
=IFERROR(VLOOKUP(B72,$B$11:$C$65,2,FALSE),"Invalid Part")
This works in my spreadsheet. Unfortunately from your initial post i didn't understand that there are part descriptions in range C11:C65 which you want to retrieve.
- stephursoJan 25, 2022Copper ContributorSorry one more thing. If Cells B72 through B150 have nothing in it, (no part number added) then the adjacent cells C72, etc. will be blank.
Ex. If I enter part number 4126 in cell B72 it displays the full part number, if not a valid part it gets the "invalid part" but if no number at all in B range then the C column is empty/blank until something is added.- OliverScheurichJan 25, 2022Gold Contributor
=IF(ISBLANK(B72),"",IFERROR(VLOOKUP(B72,$B$11:$C$65,2,FALSE),"Invalid Part"))
I entered this formula in C72 and copied it down to C150 and it works in my sheet.
- stephursoJan 25, 2022Copper Contributorworked great! Thanks again.
- stephursoJan 25, 2022Copper ContributorSorry, my fault. Sometimes I have to run it through my mind several times before I can describe it correctly. This worked perfect!!! I can't thank you enough. You're the BEST!