I need an excel file which allow user to select Category, Subcategory and subCategory1

I need an excel file which allow user to select Category, Subcategory and subCategory1 fro
For example below data:
Category Test1 Test1.1 Test2 Test2.1 Test3 Test3.1 Test4 Test4.1 Test5 Test5.1 Test6 Test6.1.1
Test1 Test1.1 Test1.1.1 Test2.1 Test2.1.1 Test3.1 Test3.1.1 Test4.1 Test4.1.1 Test5.1 Test5.1.1 Test6.1 Test6.1.1
Test2 Test1.2 Test1.1.2 Test2.2 Test2.1.2 Test3.2 Test3.1.2 Test4.2 Test4.1.2 Test5.2 Test5.1.2 Test6.2 Test6.1.2
Test3 Test1.3 Test1.1.3 Test2.3 Test2.1.3 Test3.3 Test3.1.3 Test4.3 Test4.1.3 Test5.3 Test5.1.3 Test6.3 Test6.1.3
Test4 Test1.4 Test1.1.4 Test2.4 Test2.1.4 Test3.4 Test3.1.4 Test4.4 Test4.1.4 Test5.4 Test5.1.4 Test6.4 Test6.1.4
Test5 Test1.5 Test1.1.5 Test2.5 Test2.1.5 Test3.5 Test3.1.5 Test4.5 Test4.1.5 Test5.5 Test5.1.5 Test6.5 Test6.1.5
Test6 Test1.6 Test1.1.6 Test2.6 Test2.1.6 Test3.6 Test3.1.6 Test4.6 Test4.1.6 Test5.6 Test5.1.6 Test6.6 Test6.1.6

I need dropdown on below heading :

Category SubCategory SubCategory1
Test1 Test1.1 Test1.1.1



Dropdown three.xlsx


when first dropdown select displays all value of Category Column and on the basis of this Subcategory dropdown fill while choosing and soon.


PFA and help if any body know

Hello Jaidutt,

first select all your data. Go to formulas --> name manager and click create from selection. Once you do that a window will open click Top Row.

click Ok and close the window.

select O2 then go to data tab ---> data validation. IN allow select List and in source type = categories
select P2 then go to data tab ---> data validation. IN allow select List and in source type = if(O2="","",indirect(O2))

copy paste below in Q2 then hold control and shift together and hit enter to make it array formula and release all three keys
=INDIRECT(TEXT(MAX(IFERROR(IF(SEARCH(P2,$B$2:$M$7)>0,ROW($B$2:$M$7)*100+COLUMN($B$2:$M$7))+1,0)),"R00C00"),0)

copy paste below in Q2 hold control and shift and then hit enter to make it array formula
=INDIRECT(ADDRESS(SMALL(IFERROR(IF($B$2:$M$7=$P2,ROW($B$2:$M$7)),FALSE),1),SMALL(IFERROR(IF($B$2:$M$7=$P2,COLUMN($B$2:$M$7)+1),FALSE),1)))

Hello Hemesh,

I need as displays in below image when select :

Dropdown.png

select Q2 then go to data validation select list in source copy paste below
=IF($O2="","",INDIRECT($O2&".1"))

what actually you want to do ?

PFA

in Excel sheet 1(Result) in need to drop down in column (Category,SubCategory) on basis of sheet 2(All Cat). Please help

Jaidutt, can you upload a sample book!
with what you are seeking

what are the item for subcatagory1 for crafting

Hello Jaidutt! Find attached.

I have just separated your sub category and child category.


I have used name formulas which you can check in the formula Tab-----> Name Manager

Just drag the list down to the desired number of rows for each category/Sub category or child Category.


Hope this helps.

When your query is solved, On that time mark this thread as Solved