SOLVED

Auto-populate a cell with the contains from another cell

%3CLINGO-SUB%20id%3D%22lingo-sub-3067837%22%20slang%3D%22en-US%22%3EAuto-populate%20a%20cell%20with%20the%20contains%20from%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3067837%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20I%20would%20like%20to%20auto-populate%20information%20to%20a%20cell%20from%20another%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample.%26nbsp%3B%20In%20B2%20I%20type%20in%204122%20and%20select%20Enter.%26nbsp%3B%20The%20text%20description%20for%20item%204122%20(its%20a%20long%20part%20description)%20is%20in%20C62%20(on%20the%20same%20spreadsheet).%26nbsp%3B%20When%20I%20type%20in%20the%204122%20into%20B2%20I%20want%20it%20to%20display%20the%20text%20from%20C62.%26nbsp%3B%20I%20will%20need%20this%20for%2055%20items%20so%20if%20someone%20types%20in%204233%20into%20B2%20then%20it%20will%20reference%20that%20cell%20for%20that%20part%20description.%26nbsp%3B%20Not%20sure%20if%20this%20is%20a%20IF%20Then%20or%20statement.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20users%20have%20to%20manually%20cut%20and%20paste%20or%20add%20the%20part%20description%20in%20and%20I%20want%20to%20automatic%20that%20as%20much%20as%20I%20can%20to%20avoid%20human%20data%20errors.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3CP%3ESteph%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3067837%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hello:

 

I have a spreadsheet that I would like to auto-populate information to a cell from another cell. 

 

Example.  In B2 I type in 4122 and select Enter.  The text description for item 4122 (its a long part description) is in C62 (on the same spreadsheet).  When I type in the 4122 into B2 I want it to display the text from C62.  I will need this for 55 items so if someone types in 4233 into B2 then it will reference that cell for that part description.  Not sure if this is a IF Then or statement. 

 

Currently users have to manually cut and paste or add the part description in and I want to automatic that as much as I can to avoid human data errors. 

 

Thank you in advance.

Steph

9 Replies

@stephurso Create a lookup table as demonstrated in the attached file. Depending on your Excel version you might be able to use XLOOKUP. Otherwise you'll have VLOOKUP or INDEX/MATCH at your disposal.

Hello: @Riny_van_Eekelen 

 

This worked great! I used the VLOOKUP formula.  Just one question.  When there is no part number in B2, C2 displays #N/A.  Is there a way to suppress that so it's just blank until someone actually puts in a part number into B2?

@stephurso Then you can wrap the lot in an IFERROR, like this:

 

=IFERROR( vlookup-formula , "" )

Like this? =IFERROR(VLOOKUP(B2,Parts,2,FALSE)

I keep getting an error message.
best response confirmed by Sergei Baklan (MVP)
Solution

@stephurso In te file I sent you the formula would become:

=IFERROR(VLOOKUP(B2,Parts,2,FALSE),"")

 

See attached.

 

Worked like a charm. One more item while I have you. Is there a way to force the format of the part description to the cell? in the order section some words are bold and or highlighted in red. When I type in a part number such as 4122 the description shows but the formatting does not. Everything is just lowercase with no bold or words with color. Not a big deal but it would be nice.

@stephurso Unfortunately, not something I can help you with. If possible you probably need VBA to do so.

No worries understood. Regarding this code that you supplied which works wonderful. It is possible to add a function that if the user does not add a valid part number (one that is not in cells B11 through B65 then it would display "Not a valid Part Number"?

=IFERROR(VLOOKUP(B2,Parts,2,FALSE),"")
Try this:
=IFERROR(VLOOKUP(B2,Parts,2,FALSE),"Not a valid Part Number")
Best of luck

David