You are here:

Excel/Unhide sheet only Selected option from drop down list

Advertisement


Question
Screenshot
Screenshot  
Hi,

I am stuck to a problem, seeking help from the experts.

Having first sheet named as Data, where I have two drop down list

Grade: m4a,m4b,m5 ....
Location: Factory, Region...

Again, having 10 more sheets as such:

m4a_factory
m4a_region
m5_region
m5_factory
m6a_region
m6a_factory
... and so

Now I want to unhide and make active only that sheet, which I select from the data sheet drop down.
Example: If I select M4A from Grade dropdown and Region from Location dropdown, then M4A_Region should get unhide and active and rest will be hidden.

Thanks in advance,

Sample Sheet is enclosed. - SampleWorkbook.xlsx

Regards,
Shiven

Answer
Hi Shivendra,

This can be handled by using a macro. You could write a quick macro which can work on "Case"

Select Case = M4A
hide sheet 1, sheet 2

case = M4x
hide sheet 3, sheet 4

etc.

If you are ok with this, please send me all the drop down values and sheet names and I can write a macro and send it across.

One more question - Do you expect more sheets to be added in future? if yes, then you might want to consider that while building this macro.

Gulshan.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.