Dependent drop down lists based on dynamic data range

Hi Excel experts,

This is my first post and I am relatively new to Excel.

I have scoured the forums but haven't yet been able to find a solution to my particular problem.

I have data that resembles (I have also attached an example workbook as well if that is easier)

Column A.................Column B................Column C..................Column D.............Column E................Column F
Year.......................Quarter...................Month..................Product-Code...........[data A]................[data B]
2013.........................Q1.........................Jan........................SD-21...................13%......................0.05%
2013.........................Q2..........................Apr........................NV-12...................7%.......................6%

etc

I am hoping to have, on another sheet, a series of dependent drop boxes- Year, Quarter, Month, SKU- which after selecting then displays 'dataA' & 'dataB'.

So you could select- List 1: 2013 ----> List 2: Q1 ----> List 3: Jan ----> SD-21.................and then 13% and 0.05% are displayed.


The data will be continuously added to and so needs to be based on a dynamic data range.

I know that a pivot table is the perfect way to do this, however my bosses have requested that I do not use that format so it is easy for all staff to access.

So I think I need to construct some sort of dashboard sheet, I have experimented with OFFSET from other posts, but so far have had no joy.


Any help anyone could give would outstanding- am open to any solution....


Thanks in advance

Strud

Fantastic! thanks for the quick reply Spencer101.

One thing I would hope to be able to do is remove duplicates in the drop down lists, so that 2013, or Q1 etc would only be displayed once. This is for ease of use because the data range will eventually get very large.

Not sure if you were going to include that in your edit.

Thanks again.

Excellent.

Yeah that would be fine, not a problem at all.

Originally Posted by Spencer101

That's exactly what I was changing in the edit. I was also going to change it so the drop down list for "Month" was dependent on "Quarter". Problem with doing that is it would require changing Q1, Q2 etc. to something like Qtr 1, Qtr 2 etc.

Would that be acceptable for you?

Brilliant thanks!

That's exactly what I was after. I am now going to examine it to try to get my head around it for the future.

Thanks again for the really quick & helpful response- perfect introduction to the forum!

One final question if I may(?) would it be possible to have the same sort of process that finds and SUMS groups of data as opposed to looking up a specific reference- in the same way a pivot table could?

Thanks very much!

Originally Posted by Spencer101

How's this?

Excellent so, again I have attached an example workbook.

This time the data relates to orders. I am hoping to have the drop down lists working in the same way to narrow the data.

But instead if returning the percentages as above, it returns the sum of orders within the given time-frame.

(one order will appear per row)

So using the example work book, if I had selected 2013-->Qtr1--->Feb--->SDS-1941-Black .........the return would be 5 (taken from rows 1 & 2 of data sheet).

And ideally, to be able to display that data in some sort of dynamic chart- that might show the two months prior to, and two months after the selected date.



Any ideas...?

I really wasn't sure this was going to be feasible so that's brilliant.

It is greatly appreciated, thanks very much.

Strud

Originally Posted by Spencer101

This one will take a little longer, but it's perfectly feasible. Leave it with me and I'll get back to you ASAP.

S.

This would be added to over time. The same SKU will also end up being repeated frequently on different rows as part of the different orders (you probably got that from the workbook but just checking)

I have no idea how you managed to come up with that so quickly!

It's absolutely what I was after, down to a T, so thank-you very much for taking the time to come up with that.

It's going to take me an age to get my head around it lol, but for now I can just use the file you attached so problem solved!


Just one side issue- how would I go about changing the amount of time displayed either side of the selected month on the chart- just in case I needed to in the future?

Many thanks again, you've been so helpful

Originally Posted by Spencer101

Sorry for the delay. I had to go and do some of my real job

Anyway, attached is what I think you mean. Well certainly one way of doing it anyway.

I've included a couple of comments in red to explain it, but if you need anything explaining further, let me know.

S.