Populate List based on Dropdown selection

[PaterPen]

Hi Everyone, (see example)

I want to populate a list based on a selection from a dropdown menu. I have created the menu with 4 Stores (A,B,C,D). On the first sheet I want to see the result. The second sheet contains the data and the 3 sheet contains the values for the dropdown menu.

So logically its something like, check what is selected in the dropdown menu --> Get the value for the store that is selected and the matching product line in the matching date column.

I hope you get my explanation but the attached example might make it more clear.

Regards!!!

[mehmetcik]

First

Do not use a fixed range for your Data Validation

I created a named Range "Store" using this formula:-

Please Login or Register  to view this content.

Click on the formula tab and then name manager to see my formula.

************************************************************************************************


Then enter this formula in B6 and fill down and right.

Please Login or Register  to view this content.

*************************************************************************************************

Both the formulas look over complicated but they allow you to add items and stores without having to rebuild the formula.

[PaterPen]

First of all, thanks for your help!

Unfortunately I don't understand how that formula works and I cannot recreate it in the workbook. Always shows error messages...

[PietBom]

With AutoFilter and formulas: Subtotal() you can get these results.

[PaterPen]

Thanks!

I'll try to figure out the formula with indirect but this is definitely a decent backup solution.

Kind Regards

[Miskondukt]

Thanks!

I'll try to figure out the formula with indirect but this is definitely a decent backup solution.

Kind Regards

IMHO I think you'd be better off formatting your data in a viable format that you can create an easy pivot with a slicer for.

[PaterPen]

Thanks guys!

I finally managed to get the Offset function to work, did not mark the stores properly. Thanks for all your help, the Slicer is definitely helpful generally but in this case (the data has a stupid format when I get it ) that function works better.

Well, thanks again and until next time!