Solved

Excel List

Posted on 2014-01-22
5
205 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
  • 3
  • 2
5 Comments
 
LVL 31

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 31

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 31

Accepted Solution

by:
Rob Henson earned 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now