Excel 2000 - Dynamically creating data driven columns

[cyberbob2]

Hello, I am using MS Query to pull external data conditionally which is working fine. I have a field where the user enters an order number, and associated fields for that order number (row) from the Orders table (SQL Server 2000) populate the cells as I have them mapped. Now I am trying to dynamically create sheet columns based on a count of products per order. For instance, for an order number there will be several products. So, when I enter an order number, I need to generate columns for each product for that order, with column headers like 'Product1', 'Product2', etc. So, I assume I will need a join between the Orders and Products table, then loop through the products associated for that order. I've never done anything like this in Excel so I'm not 100% sure this is possible the way I'm explaining it. Maybe there is a better approach I'm not even aware of and if so, I would welcome any advice. This group has been very helpful with my questions in the past and I appreciate you all sharing your expertise.
Thanks in advance,
CB

 

[SkipVought]

CB,

How many rows of data are being returned?

You could do a PivotTable on the resultset.

You can query your own workbook/sheet to return another resultset that has the unique Products and counts.



Skip,
[sub]
[red][/red]
[/sub]

 

[cyberbob2]

Skip,

The rows of data being returned will vary each time. A PivotTable is something I never thought of, but I like the idea of simply querying the sheet to return another resultset with what I need. Could you show me an example of this or point me in the direction of where I might be able to find an example of this? Is this something I would do with a VLookup between sheets? Is there an ADO solution?

Thanks,
CB