How to display a worksheet if a criteria is met

[ES79]

Hi,

I'm having an excel file containing multiple worksheets named according to countries.
I would like to create a more elegant solution whereby a user would have only 1 worksheet where he would specify the country he is interested in (from drop-down menu) and depending on the country selected, the right worksheet would be displayed (otherwise, I would keep them hidden).
Is there a way to do this displaying of the right worksheet using a formula (I am not familiar with VBA).

Thanks,

Elvir

[Harribone]

If every sheet has the same layout you can create a master sheet and make the cells show the relevant country data. Copy a sheet then change all the cells which will change according to country to the below (this one is cell E1)

=INDIRECT(A1&"!E1")

A1 on the master sheet for the above example is the name of the country as it appears on the tab name. A1 can be a drop down list for each country to limit the selction. You can then hide the countries to leave one front end sheet.

Hope this helps

EDIT
Country.xlsx
Attached basic example of what i mean (tabs not hidden though)

[arlu1201]

How about attaching a sample file so we can help you with it?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Kevin UK]

Hi ES79

As arlu1201 points out, attaching a sample sheet. But I think for what you require, it might be best posting in the vba forum.

http://www.excelforum.com/excel-programming-vba-macros/

[arlu1201]

Kevin,

If the OP wants a macro solution, we can have the thread moved. But i wouldnt suggest that he create a new thread in the programming sub forum as it will result in duplicate threads.

[ES79]

Thanks Harribone - it seems quite a good approach, given the same layout and restriction not to use VBA. Like the provided example

[ES79]

The idea was not to use VBA, hence in this forum. Harribone provided an acceptable input. Appreciated guys!

Kevin,

If the OP wants a macro solution, we can have the thread moved. But i wouldnt suggest that he create a new thread in the programming sub forum as it will result in duplicate threads.

[arlu1201]

Ok great, you got your question solved.

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.