Solved

# Display Excel details based on user input

Posted on 2014-03-31
168 Views
Hi,

This is a simplified version of a post I made a couple of hours ago.

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
0
Question by:Patrick O'Dea
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 22

Accepted Solution

Flyster earned 500 total points
ID: 39968348
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)

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

Author Closing Comment

ID: 39968540
Flyster,

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

LVL 22

Expert Comment

ID: 39969825
Thank you. Glad I could help!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
###### Suggested Courses
Course of the Month5 days, 11 hours left to enroll