SOLVED
Home

Data Validation to Auto-Populate Adjacent Cell

%3CLINGO-SUB%20id%3D%22lingo-sub-808691%22%20slang%3D%22en-US%22%3EData%20Validation%20to%20Auto-Populate%20Adjacent%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808691%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20afternoon!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20very%20new%20to%20using%20formulas%20and%20data%20validation%20in%20Excel%2C%20so%20I%20apologize%20if%20this%20question%20has%20been%20answered%20already%20and%20I%20didn't%20catch%20it.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI'd%20like%20to%20see%20how%20to%20use%20Data%20Validation%2C%20a%20formula%2C%20or%20a%20combination%20of%20the%20two%20to%20be%20able%20to%20enter%20a%20specific%20number%20in%20one%20cell%20so%20that%20the%20information%20tells%20the%20adjacent%20cell%20to%20then%20auto-populate%20with%20the%20appropriate%20corresponding%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20sheet%20with%207%2C864%20rows%20of%20accounts%20%26amp%3B%202%20columns%20(screenshot%201).%20As%20you'll%20see%20in%20screenshot%202%2C%20I%20need%20the%20data%20from%20the%20sheet%20(screenshot%201)%20to%20validate%20the%20data%20in%20columns%20J%2C%20K%2C%20L%2C%20%26amp%3B%20M%20(screenshot%202)%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSPAN%3EColumns%20J%20%26amp%3B%20L%3A%20this%20is%20where%20I%20will%20type%20an%20Account%20Number.%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EColumns%20K%20%26amp%3B%20M%3A%20this%20is%20where%20the%20Account%20Name%20that%20corresponds%20to%20the%20Account%20Number%20needs%20to%20go.%20%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSPAN%3EMy%20goal%20is%20to%20be%20able%20to%20type%20in%20an%20Account%20Number%20in%20Columns%20J%20%26amp%3B%20L%20and%20for%20that%20to%20tell%20the%20next%26nbsp%3B%20column%20of%20the%20same%20row%20(Columns%20K%20%26amp%3B%20M)%20auto-populate%20with%20the%20correct%20Account%20Name.%20All%20of%20this%20would%20be%20done%20using%20the%20the%207864%20rows%20of%20data%20in%20screenshot%201.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAny%20(very%20dumbed%20down)%20assistance%20you%20can%20offer%20is%20GREATLY%20appreciated!!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-808691%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Edata%20validation%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-808956%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20to%20Auto-Populate%20Adjacent%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-808956%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393599%22%20target%3D%22_blank%22%3E%40abrewer1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20understood%20from%20your%20post%20is%20that%20you%20need%20corresponding%20data%20upon%20inputting%20Account%20Number.%20You%20may%20achieve%20this%20by%20using%20%3CSTRONG%3EIndex%3C%2FSTRONG%3E%26amp%3B%20%3CSTRONG%3EMatch%3C%2FSTRONG%3Efunction.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20file%20is%20attached%2C%20please%20let%20me%20know%20if%20this%20works%20for%20you%20or%20otherwise.%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809027%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20to%20Auto-Populate%20Adjacent%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809027%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20really%20appreciate%20you%20following%20up%20with%20me%20so%20quickly%20on%20this.%20I've%20tried%20your%20suggestion%2C%20but%20instead%20of%20having%20the%20content%20that%20you%20have%20in%20columns%20A%20%26amp%3B%20B%2C%20I've%20moved%20it%20to%20another%20sheet%20within%20the%20same%20workbook.%20I'm%20not%20sure%20if%20that%20is%20the%20reason%2C%20but%20I'm%20getting%20an%20%23N%2FA%20error.%20The%20formula%20I'm%20using%20is%26nbsp%3B%3DINDEX(Sheet2!%24B%242%3A%24B%247864%2CMATCH(J2%2CSheet2!%24A%242%3A%24A%247864%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20a%20new%20screenshot%20so%20you%20can%20see%20the%20formula%20and%20error.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809037%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20to%20Auto-Populate%20Adjacent%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809037%22%20slang%3D%22en-US%22%3EI%20figured%20out%20the%20issue!%20It%20was%20because%20the%20data%20in%20Sheet2%20was%20inserted%20as%20%22From%20Text%2FCSV%22.%20I%20went%20back%20and%20inserted%20the%20data%20in%20Sheet2%20as%20just%20a%20copy%2Fpaste%20and%20it%20worked!!%20You%20just%20made%20my%20job%20so%20much%20simpler!%20Thank%20you!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809310%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20to%20Auto-Populate%20Adjacent%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809310%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393599%22%20target%3D%22_blank%22%3E%40abrewer1%3C%2FA%3E%26nbsp%3BYou%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
abrewer1
New Contributor

Good afternoon!

 

I'm very new to using formulas and data validation in Excel, so I apologize if this question has been answered already and I didn't catch it. 

I'd like to see how to use Data Validation, a formula, or a combination of the two to be able to enter a specific number in one cell so that the information tells the adjacent cell to then auto-populate with the appropriate corresponding information.

 

I have a sheet with 7,864 rows of accounts & 2 columns (screenshot 1). As you'll see in screenshot 2, I need the data from the sheet (screenshot 1) to validate the data in columns J, K, L, & M (screenshot 2)

  • Columns J & L: this is where I will type an Account Number.
  • Columns K & M: this is where the Account Name that corresponds to the Account Number needs to go.

My goal is to be able to type in an Account Number in Columns J & L and for that to tell the next  column of the same row (Columns K & M) auto-populate with the correct Account Name. All of this would be done using the the 7864 rows of data in screenshot 1.

 

Any (very dumbed down) assistance you can offer is GREATLY appreciated!!

4 Replies
Solution

Hi @abrewer1 

 

What I understood from your post is that you need corresponding data upon inputting Account Number. You may achieve this by using Index & Match function.

 

Sample file is attached, please let me know if this works for you or otherwise.

Tauqeer

@tauqeeracma 

 

I really appreciate you following up with me so quickly on this. I've tried your suggestion, but instead of having the content that you have in columns A & B, I've moved it to another sheet within the same workbook. I'm not sure if that is the reason, but I'm getting an #N/A error. The formula I'm using is =INDEX(Sheet2!$B$2:$B$7864,MATCH(J2,Sheet2!$A$2:$A$7864,0))

 

I've attached a new screenshot so you can see the formula and error. 

 

Thanks so much!

I figured out the issue! It was because the data in Sheet2 was inserted as "From Text/CSV". I went back and inserted the data in Sheet2 as just a copy/paste and it worked!! You just made my job so much simpler! Thank you!!

@abrewer1 You are welcome