Excel 2007 : Select a named range for display from a dropdown menu

**FaurKris** · 01-11-2012, 11:28 AM

Sorry if the title is confusing...

I have a really long spreadsheet. The spread sheet lists the Work Centers for the whole shop. There are line items for each Work Center that can expand to N items. Each line item is spread over multiple columns.

Ex:
Work center Title
item 1
item 2
item 3
item N

Work Center Title
item 1
item 2
item N

Etc...

As of now, I have only grouped each Work Center in its entirity so I can at least keep them all closed for faster browsing. I have also named each range.

What I would like to do is create a drop-down that displays all the Work Center names (Broach, Lathe, CNC, etc), Select the Work Center I want to view (for editing/updating), and ONLY display the selected Work center and all it's pertaining rows of data.

Is this possible?

**royUK** · 01-11-2012, 11:35 AM

Look at Custom Views

**Miraun** · 01-11-2012, 11:39 AM

Hi Faurkris;

I don't understand how you arranged your data... are all of those work centers in their own workbook, or just 1 column in the same workbook?

If they're all in one single column, you can use the auto-grouping function to help collapse the workbook into a more manageable view.

If they're in separate worksheets or workbooks, what you're looking for is a VBA macro that is triggered on the update event on the cell with the dropdown of the named ranges.
How you arrange your data will determine what the VBA will require, whether it's a dynamic hiding/unhiding of particular columns, switching the activecell to another sheet, or opening up an entirely separate workbook.

Disregard... Roy's answer is much better than mine....