Solved

Display Excel details based on user input

Posted on 2014-03-31
3
164 Views
Last Modified: 2014-04-01
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
0
Comment
Question by:Patrick O'Dea
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
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)

  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
 

Author Closing Comment

by:Patrick O'Dea
ID: 39968540
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
 
LVL 22

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now