Place text in a specific cell based on two separate drop down list entries

Hi all,

New to VBA and tried looking in a lot of places so apologies if this has been answered elsewhere.

I have two drop down lists on a spreadsheet and a button entitled 'enter'. What I want to happen is for when the user clicks 'enter' some text appears in the same row but a few columns right of a row specific to the data in row A.

So:

In cell G3 I have a drop down list called 'names' which references cells A3:A32 (a class list). And in cell G4 I have another drop down list which has a range of possible intervention strategies in a short code (eg. DT for BfL). This is stored in a table to aid data validation and there is a last column in the data validation table which is the full term for the intervention short code (eg. "A number of detentions set as a result of poor Behaviour for Learning").

The data validation table is as follows:
Intervention_tbl6 - refers to ='Pro Int Template T1!'$H$2:$J$31
PupilNames_dd - refers to =Intervention_tbl6[Names]
InterventionShortCode_dd = refers to =Intervention_tbl6[Intervention short code]
$J$2:$J$31 has the full terms for the intervention short code so if using VLOOKUP for example it would be in the column next to the short code.

My aim is for a user to select a name in the drop down in G3, select a code in G4, click enter and for the full term to appear in column F in the same row as the name that the user selected. (eg. User selects A.N.Other who appears in row 15, user selects 'DT for BFL' and "A number of detentions set as a result of poor Behaviour for Learning" appears in cell F15 once they have clicked enter.

Hope that makes some sense! If anyone could point me in the direction of an example of what I'm looking for or has any tips I'd be very grateful!

Thanks in advance.

Example Progress Template.xlsm

Of course! Sorry, should have done that earlier. Now attached.

If possible add it to end of last comment. So people can add more than one comment.

Many thanks for your reply. The table is not finished but eventually column I (Intervention Short Code) will be populated with about 20 similar 'codes' (shorter versions of the full message) and each Short Code will have its corresponding message in column J (Intervention). It was my intention to populate the cell in column F with with the full message (column J).

Eg. If the user selects 'name 12' in G3 and 'DT for HL' in G4 and clicks enter then F14 (name 12 in column A) will show the message "A number of detentions set as a result of incomplete Home Learning".

What formula could I use in column F to achieve this? Would a VLOOKUP work?

Really appreciate your help.

Bulina2K, I must apologise. I tried your code on my original spreadsheet (one uploaded here different due to sensitive info) and it did not work. I realise now this is because two columns are different. Your code works perfectly on uploaded spreadsheet providing me with correct value in column F.

Thank you both very much.

Quick question: Using your code Bulina2K, if I enter two different pieces of information to the same name, the two messages are split by a line. For formatting purposes only, is there a way I can get them to be split by a space instead?

Hmm, I've tried to copy your code above to my original spreadsheet but it is not working. I have copied into as a new sub and assigned the macro to the button. All of the information is in exactly the same places as the example I uploaded and the only difference is the Name 1 etc are actually names. Any ideas why this is not working now? Is there something in the code which relates solely to that example sheet??