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
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
