SOLVED
Home

VLOOKUP not working with CELL

%3CLINGO-SUB%20id%3D%22lingo-sub-216806%22%20slang%3D%22en-US%22%3EVLOOKUP%20not%20working%20with%20CELL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-216806%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20having%20trouble%20with%20this%20project%20that%20I'm%20working%20on.%20I'm%20trying%20to%20make%20a%20report%20in%20which%20you%20chose%20a%20name%20from%20a%20drop%20down%20list%20and%20then%20all%20of%20the%20other%20information%20is%20filled%20in%20automaticly.%20But%20I'm%20also%20trying%20to%20make%20the%20drop%20downs%20auto%20search%20when%20you%20enter%20something%20(like%20only%20part%20of%20a%20word)%20to%20shorten%20the%20list%20of%20availible%20options.%20I've%20gotten%20the%20auto%20search%20to%20work.%20But%20now%20I'm%20having%20trouble%20with%20%3CSTRONG%3E%3CEM%3Evlookup%3C%2FEM%3E%3C%2FSTRONG%3E.%20It%20won't%20read%20information%20from%20my%20%3CSTRONG%3E%3CEM%3Ecell(%22contents%22)%3C%2FEM%3E%3C%2FSTRONG%3E.%20I'm%20using%20%3CSTRONG%3E%3CEM%3Ecell(%22contents%22)%3C%2FEM%3E%3C%2FSTRONG%3E%20to%20fill%20all%20of%20the%20rows%20in%20the%20colum%20to%20get%20searchable%20drop%20downs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%20I'm%20not%20locked%20on%20this%20method%2C%20so%20if%20any%20of%20you%20have%20a%20better%20way%20of%20doing%20this%2C%20I%20can%20change%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20the%20example%20file.%3C%2FP%3E%3CP%3ESheet1%20is%20the%20%22report%22%20and%20Sheet2%20is%20the%20%22data%20base%22.%20I've%20colored%20each%20function%20with%20its%20own%20color.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-216806%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-217118%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20not%20working%20with%20CELL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-217118%22%20slang%3D%22en-US%22%3EThanks%20Sergei%20that%20works%20great.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-216905%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20not%20working%20with%20CELL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-216905%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Joe%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20it%20works%2C%20but%20with%20some%20limitations.%20cell(%22contents%22)%20without%20the%20reference%20returns%20the%20content%20of%20last%20edited%20cell.%20Thus%20you%20need%20double%20click%20the%20cell%20in%20sheet%201%2C%20start%20typing%20any%20characters%2C%20select%20drop-down%20list%20and%20it%20shows%20now%20the%20list%20of%20words%20with%20that%20characters.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20since%20you%20lookup%20the%20column%20in%20the%20middle%20of%20your%20sheet%202%20array%20I'd%20suggest%20to%20use%20INDEX%2FMATCH%20in%20sheet%201%2C%20as%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

 

I'm having trouble with this project that I'm working on. I'm trying to make a report in which you chose a name from a drop down list and then all of the other information is filled in automaticly. But I'm also trying to make the drop downs auto search when you enter something (like only part of a word) to shorten the list of availible options. I've gotten the auto search to work. But now I'm having trouble with vlookup. It won't read information from my cell("contents"). I'm using cell("contents") to fill all of the rows in the colum to get searchable drop downs.

 

Any ideas? I'm not locked on this method, so if any of you have a better way of doing this, I can change it.

 

I attached the example file.

Sheet1 is the "report" and Sheet2 is the "data base". I've colored each function with its own color.

2 Replies
Solution

Hi Joe,

 

In general it works, but with some limitations. cell("contents") without the reference returns the content of last edited cell. Thus you need double click the cell in sheet 1, start typing any characters, select drop-down list and it shows now the list of words with that characters.

 

And since you lookup the column in the middle of your sheet 2 array I'd suggest to use INDEX/MATCH in sheet 1, as attached.

Highlighted
Thanks Sergei that works great.
Related Conversations
pull data based on dropdown list
bbombb in Excel on
1 Replies
Vlookup formula - partial text search
fredericomarquez in Excel on
2 Replies
URGENT Excel question HELP
RileyCDeason in Excel on
1 Replies
Need FORMULA help please!!
marislav in Excel on
4 Replies
Sum of Vlookup results
dleesBLX in Excel on
1 Replies
vlookup
BowlingAlleyBob in Excel on
3 Replies