Linking drop down box with other cells

[Darkcloud617]

Hello,

I am brand new to this forum, so thank you for letting me join! I had a question that I just cannot seem to figure out. I have some knowledge of excel but I find it hard to navigate through VLookup which is what I think I may need for what I am trying to do.

Essentially, I have a drop down box that I want linked to a specific cell that contains a sentence. This sentence will have information linked from other cells to actually complete the sentence using these commands {="Hello "&B2&" I am looking for "&B3}. Essentially I need when someone hits a certain box, in the drop down box, it takes that sentence and pastes it into another cell. As an example if someone selects 'Disputes' in the drop down box I need for the sentence dealing with disputes to paste in a different cell. Hopefully that makes sense. I will post what I have so far if you would like, I just need to scrub it first possibly. Please let me know if I can provide more clarification.

Thank you

 

[L. Howard]

A modest example of your data as you have now would be helpful.

Along with an explanation in plain talk what you want to happen and where you want it to happen, refering to specific cells, rows and such.

Howard

 

[Darkcloud617]

Hello,

Thank you for responding. I cannot actually attach the sheet to this post so ill try my best to explain. Essentially I am needing B8 (it is a drop down box... I created the drop down from 'validation' in the 'data menu') to link to E42 and it will paste E42 in A14. I pretty much need this to repeat. If 'Dispute' is clicked in the drop down it would link to E43 and paste it in A14. If 'Credit' is clicked in the drop down it would link to E44 and paste it in A14. And then repeat, so I can add other options myself.

If it helps, the options in the drop down box are listed to the corresponding 'A' from the cells on E. So Dispute is A43 and Credit is A44 and so on.

Thank you again for the help, I made this extravagant spreadsheet and im lost with this particular part. Please let me know if I can provide more clarification.

 

[L. Howard]

Having trouble 'seeing' how your sheet is set up"

The drop down is in cell B8 and it has a bunch of words to choose from.
There is a list of words in column A that are also in that drop down cell B8.
Cell A14 appears to be the destination cell for all E column words, when found.

Depending on the word chosen in B8, find a word in column E and paste it into cell A14.

Is this statement true?

If the drop down word selected in B8 is the same as the word in A66, then return the word in E66 to A14.

Howard

 

[Darkcloud617]

Thanks again Howard, you hit the nail on the head with that statement.

To reiterate, if someone clicks 'disputes' in the drop down it will search for the word 'disputes' in A and if found (example: the word 'disputes' is in A42) it will paste the words from E42 to A14. If they click the word 'Credits' and it is in A43 it will paste E43 to A14.

*The only thing is that the words in 'A' are already used in the actual drop down- im not sure if that may be an issue or not.

You're Awesome for helping me out!!!

 

[L. Howard]

Try this, copy to the sheet module.

The code will run whenever you select a value in cell B8.

I am still confused about the column A set up with the words.
The code is set to look for the match of the B8 cell value upon selection, in column A from A2 to the very bottom (the last entry) in column A. AND return the E column value of the row of the found word to cell A14.

If you are using the column A as the source for the drop down in B8, then it is likely the ever changing A14 value (from E column) is now included in the B8 drop down as a selection candidate. With the next selection in B8, then a new E column word is in A14 and it is then in the list for B8's source.
Not sure if that is a problem or not??

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B8")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim aRng As Range
Dim LRow As Long
Dim bWord As String

bWord = Target.Value

With ActiveSheet

   LRow = .Cells(.Rows.Count, "A").End(xlUp).Row

   Set aRng = .Range("A2:A" & LRow).Find(bWord, LookIn:=xlValues)
 
  If Not aRng Is Nothing Then
   
    Cells(14, 1) = aRng.Offset(, 5)
   
   Else
    
    MsgBox "No match found for - " & bWord
     
  End If
         
End With

End Sub

 

[Darkcloud617]

Thank you for giving me the code. I seem to be having some issues, however, whenever I place the code into the sheet module it simply doesn't do anything when I try to use the drop-down. I think I must be missing something. Is there a way to 'activate' the code or something because it seems like it should be doing something that is noticeable.