How to use Dropdown menu and scollbar tools to extract data from a worksheet

twinny

New member
Joined
Jul 1, 2011
Messages
7
Reaction score
0
Points
0
Hello All,


Attached is a sample sheet of the task i am working on presently.

I have two worksheets(presentation and data) in my workbook(sample sheet).

I need the drop down and scrollbar tools to determine the data that will be extracted from the Data worksheet.

The Scrollbar represents the different weeks Week1 to Week 5, while the Drop down menu is for selection of Year.

I tries using the index and match formula but i am in a fix.

Any assistance rendered will be highly appreciated.Thank you.


Thank you
 

Attachments

  • samplesheet.xlsx
    13.4 KB · Views: 31

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
To be honest, I'd actually do this way different. I've attached a version using a PivotTable to do the work for you. Entering data in your table (the Data) page, will look a little different, but once you go to the PivotTable, right click it and select Refresh, I think you'll find it's pretty slick.

I also put in slicers to control the date/week as well as the PivotTable page fields. Not sure which version of Excel you're using, so you may not see the slicers.
 

Attachments

  • xlgf451-1.xlsx
    26.2 KB · Views: 47

twinny

New member
Joined
Jul 1, 2011
Messages
7
Reaction score
0
Points
0
Thank you Ken Puls,

I use office 2007, could you please help me work around another way of controlling the date week without the use of slicers since slicers only work in excel 2010.

Twinny
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Sure. You can use report filters instead. They work the same as slicers, they just aren't as pretty.
 

Attachments

  • xlgf451-2.xlsx
    24.1 KB · Views: 37
Top