Y
yowzers
This is a bit complicated to explain but I'll try my best. In columns A, B,
C I have different drop down lists. Column A has Store1, Store2, Store3,
etc. Column B has Dept1, Dept2, Dep3, etc. Column C has ProductA, ProductB,
ProductC. As of right now, these lists are not dependent on each other, I
can choose anything from any list regardless of the previous category. Also,
the length of these lists is undefined, meaning I will constantly be adding
to them in sequential rows below. And then columns D and beyond have data
such as Sales, Profits, # of items, etc.
What I want to do is create a table on a separate sheet with subcategories
for each combination of lists. Ideally, this would look like: Column A on
the table would be the same as the categories for Column A in the data. Then
Column B would have subcategories for when A and B are chosen. Then Column C
would have subcategories for when A B and C are chosen. So basically in the
table, A1 would say Store1, Cell B2 would say Dept1, then cell C3 C4 C5 would
say ProductA, ProductB, ProductC respectively. Then cell B6 would say Dept2,
then cell C7 C8 C9 would say ProductA, ProductB, ProductC respectively. And
so on and so forth.
Store1
Dept1
Product A
Product B
Product C
Dept 2
Product A
Product B
Store 2
Dept2
Product A
Product B
Product C
Dept 3
Product A
Product B
It would be simple to create this table if the number of possible choices
was defined, meaning I would only have 5 stores, 5 departments, and 5
products. But the number of stores, departments, and products is dynamic and
will continue to grow. Is there anyway for excel to create this table for
me? And recreate it every time I add a new Store, Dept, or Product? I hope I
was able to make this clear. Thanks in advance for any help.
C I have different drop down lists. Column A has Store1, Store2, Store3,
etc. Column B has Dept1, Dept2, Dep3, etc. Column C has ProductA, ProductB,
ProductC. As of right now, these lists are not dependent on each other, I
can choose anything from any list regardless of the previous category. Also,
the length of these lists is undefined, meaning I will constantly be adding
to them in sequential rows below. And then columns D and beyond have data
such as Sales, Profits, # of items, etc.
What I want to do is create a table on a separate sheet with subcategories
for each combination of lists. Ideally, this would look like: Column A on
the table would be the same as the categories for Column A in the data. Then
Column B would have subcategories for when A and B are chosen. Then Column C
would have subcategories for when A B and C are chosen. So basically in the
table, A1 would say Store1, Cell B2 would say Dept1, then cell C3 C4 C5 would
say ProductA, ProductB, ProductC respectively. Then cell B6 would say Dept2,
then cell C7 C8 C9 would say ProductA, ProductB, ProductC respectively. And
so on and so forth.
Store1
Dept1
Product A
Product B
Product C
Dept 2
Product A
Product B
Store 2
Dept2
Product A
Product B
Product C
Dept 3
Product A
Product B
It would be simple to create this table if the number of possible choices
was defined, meaning I would only have 5 stores, 5 departments, and 5
products. But the number of stores, departments, and products is dynamic and
will continue to grow. Is there anyway for excel to create this table for
me? And recreate it every time I add a new Store, Dept, or Product? I hope I
was able to make this clear. Thanks in advance for any help.