help with dynamic tables

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.
0
Utf
12/14/2009 10:47:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
636 Views

Similar Articles

[PageSpeed] 8

Try

http://www.contextures.com/xlDataVal02.html



"yowzers" wrote:

> 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.
0
Utf
12/14/2009 11:15:01 PM
Hi,

Try this

http://www.contextures.com/xlDataVal02.html

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"yowzers" <yowzers@discussions.microsoft.com> wrote in message 
news:C3FBB3B8-1CF3-4001-BCBC-B623E2A5D101@microsoft.com...
> 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. 

0
Ashish
12/14/2009 11:15:28 PM
Thanks for the reply but I'm not trying to make dynamic lists.  I don't need 
one category to be a subcategory of another.  All three can be a combination 
of each other.  What I needed was to create a table with all combinations.  
Thanks!

"Ashish Mathur" wrote:

> Hi,
> 
> Try this
> 
> http://www.contextures.com/xlDataVal02.html
> 
> -- 
> Regards,
> 
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
> 
> "yowzers" <yowzers@discussions.microsoft.com> wrote in message 
> news:C3FBB3B8-1CF3-4001-BCBC-B623E2A5D101@microsoft.com...
> > 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. 
> 
0
Utf
12/15/2009 4:40:14 PM
Reply:

Similar Artilces:

Help with SQL (Access2007)
Hello. I am trying to integrate data from several sites into 1 (new) table. In order to distinguish the data from each site in the new table I have a field (InstID) which holds the Instution number of the site. The fields from the old site tables and the new table are identical except for the InstID. InstID and ClientID are Primary Keys. The path to the old table is asked, then the number for the InstID is asked and placed as a variable - varInstID. I have an append sql as follows: Private Sub UpdateDB_Click() ' populate the clients table strSql = "INSERT INTO tblClients ( In...

forecast function help
This might seem a bit newbie but im having trouble with the forecas function. Say for example i have a collection of data for sales of each item ove a number of years: item 2000 2001 2002 2003 1 3 4 5 2 4 3 2 3 2 2 4 4 3 1 5 5 4 1 6 6 2 2 4 i am asked to forcast the values for the year 2003. I have read an looked at many examples on how to do this and still can't work ou where to start? Any help would ...

the criteria pane and table panes have disappered.
In my excel file I can't see the criteria and table panes and the functions will not reappear. ...

Need help with Excel Form & ComboBox Tutorial
At http://www.excel-vba.com/v-forms-controls.htm I have followed instructions... my code on the form is below but it won't run... I've marked the error... Can anybody give me any help with this? thanks Code is below-------------- Private Sub cmdBtnSubmit_Click() shReport.Range("C4").Value = cbxCity.Value cbxCity.Value = "Select a City" frmCity.Hide End Sub Private Sub cmdCityCancel_Click() cbxCity.Value = "Select a City" frmCity.Hide End Sub Private Sub UserForm_Activate() shParameterst.Activate '<-----Run Time Error 424 - Object Required...

Merging tables
Hello, I do not have deep knowledge of Excel. That is why my proble might appear a bit strange: I have two tables, list1 and list2. In both tables there is a colum with unique ID numbers. The rest of the columns contains differen information in both tables. Example: list1 ----- ID name email 101 bob bob@none.com 102 rita rita@none.com ... 999 jim jim@none.com list2 ----- ID salary 102 2000 103 2500 ... 204 2100 What I would like to get as a result is a combined table, with selecte columns from both tables in it. I.e.: list3 ----- ID name salary 101 bob - 102 rita 2500 ... Anyone who can ...

Customer Address Help
Hi, I was wondering if anybody knew of a way to run a customer report that invluced the customers address, city, state, and zip in it. I am not very familiar with crystal reports, so if there is another way that would be awesome. Thanks, -Bill H On CustomerSource there is a section called the "Report Library"- I think it's under downloads. In the RMS Report Library MS has provided several new or modified reports, including one with customer address. On Sat, 17 Jul 2004 08:40:10 -0700, Bill H <bill@platinumpools.com> wrote: > Hi, I was wondering if anybody k...

Macro Help/Duplicate Items + Insert Rows + Sum
I am trying to create a template that will do the following: 1. Find Duplicate Entries (AlphaNumeric) In A Column 2. Insert 2 Rows Between The Duplicate Entries Then: 1. Sub-Total(Another Column With Random Numbers) Of The Duplicate Entries 2. Format the Sub-Total In Bold I have gotten to the point of writting a macro that will identify the duplicate entries; does anybody know how to do the rest? This is a changing set of data, transferred to excel from a relational database (Lotus123 Rel2, which contains anywhere between 3000 to 5000 rows. I cannot spend time grouping the data ...

Help with graph / chart
I have a graph for weeks 1-52, I have split this into 4 seperate graphs each showing a quarter (13 weeks) I cant remember exactly how I created them but possibly using some sort of copy paste as each chart show weeks 1 - 13 along the bottom. This should read......... for chart 1 1-13 for chart 2 14 - 26 chart 3 27 - 40 chart 4 41 - 52 How do I change this on each chart to read the week numbers indicated.? thanks Hi, You need to define the Category labels for the chart. Chart 1 is fine as it defaults to the values 1 to 13. For the other 3 charts you will need to create...

unable to load help topic
Using Money 2004 deluxe. Asking for help I get "unable to load topic" try again. No help, same responce. I went to MS Knowledge base article 812755. Which says 'clear the cache' Which I did. No help, still 'unable to load topic' Tried asking a 'Microsoft pro', could not get a screen to ask my question. Any suggestions? I cleared both MS IE and my default browser, and tried again, still no help. Seems like I should be able to get 'HELP' I even reloaded the Money program, still no HELP. Thanks for any 'HELP" Walt In microsoft.public.money...

Pivot Table fields #2
Is there a way to format multiple pivot table fields at one time and have them refresh that way, rather than having to select each column and choose the format options? Thanks CLou: I think the solution for you is to open the Pivot Table Toolbar and select the Field Settings icon. This sets the format for all results for that particular field. One limitation that I haven't found a way around is getting a preferred column size to "stick" and not reset after a table refresh. Bruce >-----Original Message----- >Is there a way to format multiple pivot table fields at ...

Copy chart with reference table
I have a pie chart neatly tied to a data table alongside. Now I want to copy both the chart and table to a different area of the sheet so I can modify the new data table and have 2 different charts. My issue is that the chart will only allow absolute references, so when I copy both the chart and table together the new chart still is tied to the old table - so I have to go and change the source data ranges one by one. Any way that I can do this easily? Copy the whole sheet. The copied chart links to the data on the copied sheet. Now cut the range that includes the copied char...

Pivot Table Refresh Problems
Hi All: Hoping that someone can help. I am trying to refresh a pivot table using the following code which was inserted on the Daily Production Output Sheet(both sheets in the same workbook). I am using Excel 2003 Private Sub Worksheet_Calculate() 'If data on this worksheet changes, refresh the pivot table Sheets("Daily Production Output").PivotTables("PivotTable3").RefreshTable End Sub The Calculation has been set to Automatic. However when I try to run the above code I am getting the following error" Run Time error 1044, Application defined or Object defin...

Can not create Matrix Item please Help RMS 2.0
RMS 2.0 Can not create Matrix Item please Help When trying to create any new items I receive error message This is the message (-2147217864) Row Cannot be located for updating. Some values may have been change since it was last read. Manger still creates standard items but still receives message with out this number in message -2147217864 ...

pivot tables #19
I want to create a pivot table where I just have to enter the data in once where it will post to a general ledger, income/expense statement, profit & loss report and individual vendor accounts. Can this be done? -- tiki Hi tikitai, Look at my Excel Database Tutorial at http://edferrero.m6.net/DataTutor1.html You should be able to work it out form there. Ed Ferrero > I want to create a pivot table where I just have to enter the data in once where it will post to a general ledger, income/expense statement, profit & loss report and individual vendor accounts. Can this be done? &...

Massive Report: Have you ever done this? Help Please
Hi, I am compiling the results of a survey in ACC2003 as a paper appendix. I have about 60 report objects which are about 2 to 4 pages of text each. I have about 60 Pivot Charts and tables as separate form objects. I want to have one report which has the charts and tables and text in it since this would be easy to layout and the page numbering would flow right through. Is this the correct way to do it? I have made a start and the first few pages are fine with charts and tables. However, Access seems to have space restrictions on the height of a report group? When I increase the ...

SMTP Help!!!
I have a customer with a new Exchange 2003 server Single AD domain on one server DNS server local and seems to be working correctly Cable internet through Comcast Had been receiving 2012 and 2013 app log events Increased the DNSErrorsBeforeFailover as suggested in a knowledgebase article 2012 & 2013 Errors have stop but replaced with 4006 events No mail flow inbound or outbound for past 2 days!! SMTP appears busted, cannot telnet in or out on Port 25 even though firewall has port forwarding on that port Switched firewalls with same result Noticed periodic Back Orifice attack attem...

Modify Access 97 tables in Access 2003
How do I modify an Access 97 table using Access 2003 without converting the database? Is there any tool available? Rick This is only one person's experience... There is only one tool I'm familiar with that would let you do that, and it's called ... Access '97<g>! You've described HOW you want to do something. Now, if you'll describe a bit more about WHY you need this done, the folks here in the newsgroup may be able to offer more specific suggestions. Regards Jeff Boyce Microsoft Office/Access MVP "Rick" <Rick@discussions.microsoft.com...

Nesting? How do I create a table to reference?
Tried to nest more than 8 componets but it did not work. Any help on creating a table to reference it would be appreciated. Never done it before. Tried the Excel help, and it was worthless. Here is my original post that explains more: http://www.excelforum.com/showthread.php?t=498860 -- mcr1 ------------------------------------------------------------------------ mcr1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15496 View this thread: http://www.excelforum.com/showthread.php?threadid=498905 ok you have a two column table.starting in the lh column you...

Macro Error, annoying plz help?
Hi, new to this board and kinda new to Excel as well. I created an excell file which includes various (difficult) calculations. And it's all finished and ready for distribution :P Cept for 2 minor things which i can't seem 2 fix. The most important one is this Macro Error which keeps popping up when you open the file. (I included a combo-box form, i think it's gotta do with that). Because if u pick something from that list (combo box) the error pops up again, very annoying of course. There is one way to prevent this as far as i could see and that was by setting macro security low....

How do I sort a pivot table
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I'm trying to do a very basic function but I can't figure it out for the life of me. Please help! ...

Basic Worksheet Help
I can't find an auto sum function in google worksheet. Can some help? These are Excel groups -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS web: www.nickhodge.co.uk blog (non-tech): www.nickhodge.co.uk/blog/ <Hollins3@googlemail.com> wrote in message news:1180278623.923893.261800@u30g2000hsc.googlegroups.com... >I can't find an auto sum function in google worksheet. Can some help? > Hollins3@googlemail.com wrote: > I can't find an auto sum function in google worksheet. Can some help? > Easy workaround:...

Help!!! Migration Problem!
Morning Guys, We're migrating from one Windows 2003 domain to another (acquisition). DomainA.lab - Forest Trust 2000, Domain Trust 2003 DomainB.lab - Forest Trust 2003, Domain Trust 2003 Migration from DomainA.lab to DomainB.lab - Trust relationship external, 2-way, Domain Wide Authentication Side Filtering disabled on both domain and I can also see the SID History attribute which is correct Problem: Users in domainA cant can't access SOME shares on domainB computers. The SIDHistory attribute in DomainB matches the SID of the group in DomainA, but still no luck. Any su...

Help
The deal: On earthlink webmail, I only receive one of each email. When I "Send/Receive" from Outlook 2000, the Send/Receive window tells me I'm receiving 10 emails, but 30 appear in my inbox. Everything arrives in triplicate or duplicate. Extended headers appear to be the same in all three identical emails. Things I've tried and know: Deleted and then reconfigured my earthlink account many times. There is only one earthlink account. "Leave a copy of messages on server" is unchecked. Turned Earhlink Spamblocker On then Off then On then Off... no change. S...

Need help with code...
I have a problem. I have a drop down combo box called "Query status" with two options: "outstanding" and "completed". The record can't be changed to "completed" until certain other fields have ALL been entered but there is an extra complication. One other combo box can be either "other" or "invoice". Two extra fields need to be entered if this combo is "invoice" otherwise they aren't mandatory. In full this is the code I currently have in the "after update" event of each field: If Me.Qry_QryType =...

How do you change to currency in a table and add columns?
I'm trying to learn to use the new Word and can't figure out in my ribbons how to change numbers to a currency format, as well as viewing the table content to be able to quick sum columns. Help?! Use Excel which is designed for that work. -- Terry Farrell - MSWord MVP "Duprey72" <Duprey72@discussions.microsoft.com> wrote in message news:ADB852A4-D4AF-44FF-8F89-AC4B110D080C@microsoft.com... > I'm trying to learn to use the new Word and can't figure out in my ribbons > how to change numbers to a currency format, as well as viewing the tabl...