Making a cell searchable & transfer values to adjacent cells

Hello excel wizards of the world, first off thank you for your time and any help you're able to offer.

Problem/Goal:
I have a spreadsheet that displays multiple item numbers, that have unique manufacturers and numbers that need to be associated with them in adjacent cells.
The manufacturer is always known, but the numbers associated with them are not (these numbers are contained in another sheet).

Is there a way to click into cell H10 (on sheet "log") and search another sheet named "lookup" in cells A2:A350 (the value in cells A2:A350 are the items visible in the search box)
When clicking the desired value (after search) I need the numbers in sheet "lookup" to be copied and pasted into sheet "log". Cells in "lookup" B2:C2 pasted to "log" K10 and L10. Cell in "lookup" D2 pasted to sheet "log" Q10.

Part 2:
The "lookup" sheet has another column with a yes/no value in E2 for each row.
After the proper numbers are moved into the specified cells above, could a dialog box pop up (if value is NO) saying "Value is false, please input value into appropriate cell"

Thanks in advance!

Hello,
Here is the workbook.

Thanks!

Hello, thanks for the help! Looks like a good start, but I have a few feasibility questions;
Typing into H10 doesn't act as a search box, requiring me to know exactly what to type into that cell (the values are usually much more complicated than 'make 1'). Ideally as I typed into the cell I would be able to scroll through a drop down and select what I was trying to find.
The search would ideally not be located in H10, but at each line item H12:300 (I'll have more than one item), and the search box in H12 would return the values I'm looking for into K12, L12, and Q12, not in row 10

Thanks for your help!

Originally Posted by xladept

I unmerged K10:L10 and Formatted Q10 as Percentage and this seems to be what you specified:

In The Log sheet module

Please Login or Register  to view this content.

Originally Posted by xladept

So is the sample book you sent still relevant?

Hello!
Yes the sample book is still relevant, the solution would just have to have the solution implemented in the body of the sheet, not the cells you were experimenting with.
The searchable cell would be in H12:H300 and return the values to the same row, K, L and Q

Thanks for helping!

Originally Posted by xladept

See if this does the trick!

It works! 8)
Ok, so. I see you have some crafty VB code in there, aside from placing that code in a module, do I need to assign special formatting to the H column to allow a dropdown as you have it designed?

As an aside- and another layer of a challenge, you see in the sheet how it says item 22 with a 'B' in column B? Then below it it has 'O'?
What if I selected the proper 'make' in column H using your solution (on the row with the 'B'), would it be possible to fill those same values (column K,L,Q) into the rows below that have 'O' and stop filling when it sees a 'B' row?

Originally Posted by xladept

The dropdown is already there - you don't need to do anything - I'll be looking at the B-O anon.

Thanks for the rep and the acknowledgement!

EDIT:

Here's the B-O - just replace the change event in the sheet module:

Please Login or Register  to view this content.

Hi Xladept, I think I got it working in my sheet, thanks for your help!
Last question, is it possible for the drop down to display more than 8 things? And is there a way to make the drop down more navigable vs only scrolling? Like if i start to type 'ad' then anything with 'ad' in the field shows up? Or maybe jumps to the items with 't' as the first letter if I type t?

Hi Xladept,
I was looking around to some other solutions that would make navigation of the drop down easier (the list in the 'lookup' sheet is ~300 items).
I found this bit of code

Please Login or Register  to view this content.

So with this code, it will bring up a drop down, and if I type the first 3 letters of what im looking for in the cell, then click the drop down, it displays similar values making the need for scrolling very small.
However I'm not sure how to interface this with the code you wrote to return the values.

Can you help me?

Thanks

Ok cool, I will put it after. Like this? I don't delete any of your code

Please Login or Register  to view this content.

I couldn't get it to work, appears to work the same as it did before.

Not that I could tell, if i started to type then click the drop down, it didn't narrow down the listed results

It seems to behave oddly, like if I go to type into the cell with the drop down, it kicks me out of the cell and to the one immediately to the upper left hand side. I think I'd prefer the snippet of code I posted earlier, it worked well enough.

Question:
If I got the drop down to return narrowed down results after first typing, then hitting the drop down- how do I then get the proper values to return to the cells I want?

Please Login or Register  to view this content.

this is the only code I'm using- its referencing

=IF(ISNUMBER(SEARCH($J$2,Lookup!A2)),MAX($F$1:F1)+1,0)
=ROWS($G$2:G2)
=IFERROR(INDEX($A$2:$A$1000,MATCH(G2,$F$2:$F$1000,0)),"")

The code I put above is looking at column H for a match

The full list is still in A, but the formulas I listed earlier return the typed 'search' values into the H column

Originally Posted by xladept

See if this does the trick!

Hi Xladept, its me again!

I had a road bump that was going to require me to change the format of my sheet, so I tabled this until the change was done. I found that I'd like to keep with the solution you presented in post 8 - no fancy search able drop downs etc...

Can you help me change the code to match the new layout? The columns shifted in both tabs. I tried following the logic of what you wrote to change it but couldn't get it!
I was also wondering if I could 'activate' the macro through a button instead of relying on running it from visual basic, so the user can run it after he sets the sheet up.

I attached a new example sheet with the new formatting

Are you able to help?

Thank you so much if you are!

Originally Posted by xladept

The test is just test code so I can test the program as it's written - you don't need it at all. The values are filling in but the font color is white so you can't see them. The "function" is triggered by the event of choosing from the drop down - as I remember, we had the font color issue before. I do, however, have a correction (the yes,no is now in D).

Xladept,
Thank you for helping me out, it works well.
Here is a question that I feel isn't something possible: Is it possible to prevent the cells that get pasted into (column L,M,R) from returning #value or blank rows AFTER you double click into the I column AND don't select anything from the drop down?
In case someone accidentally clicks into the cell.

Thank you

Originally Posted by xladept

Hi Clarkit,

Thanks for the rep!

I don't get what you mean - there is no value anything in the lookup and no event code in this sample

If I double click into the drop down column, then click away without selecting one of the values from the drop down, blank cells or #value is returned in the adjacent cells I noted in my previous reply (the #value is probably coming from the header, as they are words and not numbers which my cells are expecting)

I guess the double click sheet change event is still triggering the copy of cells, even when there is no selection

Originally Posted by xladept

Maybe:

Please Login or Register  to view this content.

Tried running it - the debug highlighted

"If Target.Column = 9 And Target.Value <> "" Then"

looks like it hung up there.

Originally Posted by xladept

I can't duplicate your problem - here's the code (the ddl gets run only once) - the code in the before sheet module and the book saved as macro-enabled:

Please Login or Register  to view this content.

I didn't elaborate when I should have - the debug highlights that line when I run a macro that copy/pastes data from one tab (where I import some data to) to the tab that this drop down is used on.
Is there a way for this drop down feature code to only be read after the import from one tab to another?

Originally Posted by xladept

Do you import into the drop-down field? Then maybe changing this line:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

Hi Xladept,
Thread revival! I was wondering if you could help me with a 'bug' that appeared. Everything seems to be working well, however anything above row 12 should be 'safe' from the functionality built here. When I delete one of those values though, it deletes those values. Which is fine, but only in the specified range below I12:I10000. If I remove values from I2 for example cell m2 is deleted.
Is there a way to limit the delete behavior to a range?

Thank you,

Please Login or Register  to view this content.

Originally Posted by xladept

Does M2 depend on I2?? My copy of your book has L2:M2 merged together

Didn't see a notification that you replied, sorry about that!

No M2 does not depend on it.
The lovely bit of code you shared with me is intended to work in the few cells i highlighted in the picture below, but is unfortunately affecting the cells in row 2. Basically by making the I column cells blank, its returning blank cells in M and N as designed. I just dont want it to have that behavior above row 12

Seems liked that did the trick, thanks!