asked on

Excel List

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

Rob Henson

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

Rob Henson

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

WTC_Services

ASKER

Hi Rob H,

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

Cheers

Mark

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WTC_Services

ASKER

Excellent prompt answer, thank you