?
Solved

Excel List

Posted on 2014-01-22
5
Medium Priority
?
244 Views
Last Modified: 2014-01-22
I would like to define a range. Top row to be categories and rows below be sub categories. Following use a drop down box or list to select category and sub category.
20140122-Experts-Exchange---cate.xlsx
0
Comment
Question by:WTC_Services
[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
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39799419
Are you trying to then Filter a data set based on your selections? Which version of Excel?

If your data is in a list/table format you might be able to use Slicers that were introduced in 2010.

In the meantime I will look at existing file.

Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39799510
See attached.

DataValidation dropdown in C21, validation list stright from top row of table.
DataValidation dropdown in C22, validation from list generated by a named dynamic data range.

Dynamic Range uses formula:

=OFFSET(Sheet1!$C$5,1,MATCH(Sheet1!$C$21,Sheet1!$D$5:$K$5,0),12,1)

This creates a list using OFFSET function:

=OFFSET(Reference, RowOffset, ColumnOffset, Height, Width)

Reference = C5, top left of table
Row Offset = 1, subcategory data starts in next row down
Column Offset = uses MATCH function to match the category in C21 within the table headers.
Height = number of entries in list, I have set arbitrarily to 12 but could be set with formula if so required.
Width = Set to 1 as you only need one column.

Thanks
Rob H
Category-and-SubCategory.xlsx
0
 

Author Comment

by:WTC_Services
ID: 39799532
Hi Rob H,

The attached Excel spread sheet does not seem to have any editing?

Cheers

Mark
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 39799582
Hi Mark,

If you put the cursor in C21 you should get a dropdown to the right hand end to select a value.

Once that value is selected, the options in a similar dropdwon in C22 will change accordingly.

Thanks
Rob H
0
 

Author Closing Comment

by:WTC_Services
ID: 39801443
Excellent prompt answer, thank you
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

801 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