• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation based on filter

TPR

Member
Dear Friends,
Please find my attached file.
From Column A to H is my data. From J to M will be my data validation based on std-1 to std-4
Column O & P is the result that will show me in descending order. Issue is when J to M is not blank, O & P will show all items' total sale figure to the customers in descending order. That means the customer who purchased highest in total (all items) will come first, then others in descending order.
Now the main tricky matter is if I select std-1 as stationery, then only soap & toothpaste will be shown in data validation. if I select soap in std-2, in std-3 only detergent & bath will come up in drop down list. if I select detergent in std-3, then liquid & bar will be in validation of std-4, now I shall select bar in std-4, then only surf excel bar & ariel bar will be in item data validation. Now if I select surf excel bar, result will show in O & P column.

It's a hierarchy type, that i need. But I may skip any std, & if I skip, sale figure will show all item's total figure which are based on std where I select.
Suppose if I select only std-1 as stationery & other std (2 to3) keep blank, then in std 4 validated figures will come liquid, bar, senior, junior, dry skin, normal. I can select any one of those, & result would be all items' sale total of that particular standard.

I tried lot but become failure, I had taken help of so many websites but not getting desired result. I badly need the solution.
Please help. I have highlighted yellow, as selected. And pink are my result. The file is an instance only. Item (data validation-5) may come about more than 3000 if I only select std-1. Result will be sorted based on my selection.
Thanks in advance.
Regards
TPR
 

Attachments

  • calculation-1.xlsx
    13.1 KB · Views: 13
Try this: =INDEX($H$2:$H$101,MATCH(1,(O3=$F$2:$F$101)*(N3=$E$2:$E$101),0)) and confirm it with Control Shift Enter which will put curly brackets {} around the formula. Do not type the curly brackets.
 
Try this: =INDEX($H$2:$H$101,MATCH(1,(O3=$F$2:$F$101)*(N3=$E$2:$E$101),0)) and confirm it with Control Shift Enter which will put curly brackets {} around the formula. Do not type the curly brackets.
Sorry, I don't think this formula will serve my purpose.
First of all I have to get filtered data in 4 nos data validations, then if I want to skip one std, then how all will be seen in next std data validation.
 
I guess I am not understanding your needs correctly. Time for someone else to try and decipher the needs here.
 
I guess I am not understanding your needs correctly. Time for someone else to try and decipher the needs here.
This one would be multiple dependant data validation. But if I don't select any std then it would show the next level's all detail.
 
Hi:

The easiest way to do this using pivot and slicers, this will give you the results as per all the random combinations you are selecting. I am not sure why you are going a formula method where you have a easy native function available in excel.

Thanks
 

Attachments

  • calculation-1.xlsx
    23.4 KB · Views: 4
Nice catch @Nebu , offering what is needed and not wanted, I dig that a lot. And a valuable lesson for many to keep it simple and as close to native functions as possible to make things work and easier maintainable.

One downside I might see, is that those slicers take up a lot of real estate.

I was thinking in the line of using power query to make those interdependent lists. In a combo with small macro code to refresh them after the first selection. Considered it too cumbersome.

If the OP is already using Office 365, he might anticipate for the release (now in beta) of the dynamic array functions FILTER, SORT and UNIQUE. Making these kind of request simply child play. Until then I'd just slice ahead...
 
Back
Top