Display Excel details based on user input

Hi,

This is a simplified version of a post I made a couple of hours ago.
If this is contrary to etiquette then please advise!

It is best explained by looking at the attached.

When the user enters one of the 3 yellow cells (i.e. chooses a country) then ... this countries details should be displayed in full in the green section.  Note that the source of all the end data is the table on the far left.

So, entering one of the yellow cell generates a "drill down" in the green area.
How can I achieve this?
EEMan2.xlsx
Patrick O'DeaAsked:
Who is Participating?
 
FlysterConnect With a Mentor Commented:
Please refer to the attached. This was accomplished as follows:

First, a new column A was added and has this formula:

=COUNTIF($C$2:C2,C2)&C2

It's copied down and the result is the Country with a sequential number in front of it. I.E 1UK, 2UK.

Column L uses this formula:

=IFERROR(VLOOKUP(1&Ctry,A:D,2,FALSE),"")

This formula returns the city number ( the "2" returns the second column in range A:D). If there is no city associated with the selection, the IFERROR returns a blank cell. Column M uses the same formula with the "2" changed to "4".

If you right-click the tab and select View Code, you'll see this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  ActiveWorkbook.Names.Add Name:="Ctry", RefersToR1C1:=ActiveCell
  Me.Calculate

End Sub

This assigns the value of the active cell to Ctry, which is used in the VLOOKUP formula.

To conform with the requirement "User will only be allowed into yellow cells", the sheet is protected.
EEMan2.xlsm
0
 
Patrick O'DeaAuthor Commented:
Flyster,

Thanks for a superb answer.
In particular, it is great because it is so relatively clean and simple.

(I need to convert this logic into my real world scenario but that's a different project!)

Thanks again!
0
 
FlysterCommented:
Thank you. Glad I could help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.