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