Monday, September 1, 2014

How to List out Sub Category Items based on Main Category in Excel

How to show Sub Category Items of corresponding Main Category in Excel
Let us suppose we have the Main Regions in Column A starting from Range $A$2.,and the Column B contains the Sub Regions starting from Range  $B$2.



Requirement :
In Column $D$2 , we have a Dropdown list of Main Regions , when we select one Main Region , then the corresponding Sub Regions have to display in the Cells following to the $D$2.which means from $D$3,$D$4..
When Sub Regions are over for that Main Region , the Formula will display '-' in the Cells.

This Can be done Using the Following Formula :
=IF(OFFSET($A$1,(MATCH($D$2,$A:$A,0)-1)+ROW()-3,0)=$D$2,OFFSET($A$1,(MATCH($D$2,$A:$A,0)-1)+ROW()-3,1),"_")

Note :
Since we start writing the Formula from $D$3 , we have use the ROW()-3 .
Similarly if we start writing the Formula from $D$10 , we have to use the ROW()-10.

Then only it will fetch the correct Sub Region corresponding that Main Region from the corresponding Row.

Example :
The following Example replicates the above Requirement.




Important Note :
This is a Very useful in Reporting Requirement.

Thanks, TAMATAM

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog