Monday, 1 September 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.


1 comment:

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts