Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating dynamic (formatted) tables
Dear all,
I built a file that imports data from an external source (excel format). The code that I wrote does not only import the data but also adds additional calculated columns. One of the sheets contains data about orders to different countries, it looks something like this : Country Order_M Ord_Nr Qty Prod. TAT_act. SLA_TAT SLA_Achieved Austria jan. 04 prf001 25 pencil 12 13 yes Austria may. 04 prf002 36 paper 10 13 yes Belgium feb. 04 prf003 78 pencil 15 11 no France feb. 04 prf004 10 paper 25 11 no France mrt. 04 prf005 15 paper 35 11 no UK jan. 04 prf006 15 pencil 8 10 yes UK apr. 04 prf007 25 paper 9 10 yes Sweden apr. 04 prf008 30 paper 10 9 no In the next sheet (sheet 2) I want to make a number of tables using the above data. This table is formatted and looks as follows: |#orders |In TAT| | |OutTAT| Country| Month |Product |placed |Total | % |SLA |total | % |SLA| --------------------------------------------------------------------- Austria| jan. 04 |pencil | 1 | 1 |100%| 95% | 0 | 0 | 3%| | may. 04 |paper | 1 | 1 |100%| 95% | 0 | 0 | 3%| Austria total | 2 | 2 |100%| 95% | 0 | 0 | 3%| Column 1: Country name (column 1 in data sheet) Column 2: Month (Oder_Month in data sheet) Column 3: Product (Product in data sheet) Column 4: #Orders placed (Count Order_Nr in sheet 1 where country_sheet1 is equal to country_sheet2) Column 5: within TAT (Count "yes" in column SLA_Achieved in sheet 1 where country_sheet1 is equal to country_sheet2) Column 6: % (within TAT divided by #orders placed) Column 7: SLA pre-defined percentage (means that a minimum of 95% of all orders must be within TAT) Column 8: Outside TAT (Count "no" in column SLA_Achieved where country_sheet1 is equal to country_sheet2) Column 9: % (outside TAT divided by #orders placed) Column 10: SLA pre-defined percentage (means that a maximum of 3% of all orders can be outside TAT) I want to generate this formatted table automatically. I never know before hand how many orders have been placed for what products and what time frame. The tables need to be dynamic. One way of doing this is via the pivot table. But can I add columns that are not in the data sheet ? (column 7 & 9). Or do I need to add these to the data sheet ? Can i have formats added automatically in the pivot ? Can any of you tell me how I can do this dynamically ? What is the code that I need to write ? If there is something that is not clear do not hesitate to let me know. Your tips & recommendations are highly appreciated. Thanks in advance ! Gr, Mariam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating dynamic (formatted) tables
Mariam,
Sounds like you are just trying to import data into Excel and than expand it. Just import it and add any columns you want. Use code for all of this. If the problem is that the number of records is constantly changing - just use variable ranges. I sprinkle the following throughout my code to get the number of rows (records): Dim lrow as Long lrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row ' this finds the last row with something in column A on Sheet(1) Range(Cells(1,1),Cells(lrow,5)).whatever or Range("A1:E" & lrow).whatever just change to suit your workbooks/sheets. hth -- steveb (Remove 'NoSpam' from email address if replying direct) "Mariam" wrote in message om... Dear all, I built a file that imports data from an external source (excel format). The code that I wrote does not only import the data but also adds additional calculated columns. One of the sheets contains data about orders to different countries, it looks something like this : Country Order_M Ord_Nr Qty Prod. TAT_act. SLA_TAT SLA_Achieved Austria jan. 04 prf001 25 pencil 12 13 yes Austria may. 04 prf002 36 paper 10 13 yes Belgium feb. 04 prf003 78 pencil 15 11 no France feb. 04 prf004 10 paper 25 11 no France mrt. 04 prf005 15 paper 35 11 no UK jan. 04 prf006 15 pencil 8 10 yes UK apr. 04 prf007 25 paper 9 10 yes Sweden apr. 04 prf008 30 paper 10 9 no In the next sheet (sheet 2) I want to make a number of tables using the above data. This table is formatted and looks as follows: |#orders |In TAT| | |OutTAT| Country| Month |Product |placed |Total | % |SLA |total | % |SLA| --------------------------------------------------------------------- Austria| jan. 04 |pencil | 1 | 1 |100%| 95% | 0 | 0 | 3%| | may. 04 |paper | 1 | 1 |100%| 95% | 0 | 0 | 3%| Austria total | 2 | 2 |100%| 95% | 0 | 0 | 3%| Column 1: Country name (column 1 in data sheet) Column 2: Month (Oder_Month in data sheet) Column 3: Product (Product in data sheet) Column 4: #Orders placed (Count Order_Nr in sheet 1 where country_sheet1 is equal to country_sheet2) Column 5: within TAT (Count "yes" in column SLA_Achieved in sheet 1 where country_sheet1 is equal to country_sheet2) Column 6: % (within TAT divided by #orders placed) Column 7: SLA pre-defined percentage (means that a minimum of 95% of all orders must be within TAT) Column 8: Outside TAT (Count "no" in column SLA_Achieved where country_sheet1 is equal to country_sheet2) Column 9: % (outside TAT divided by #orders placed) Column 10: SLA pre-defined percentage (means that a maximum of 3% of all orders can be outside TAT) I want to generate this formatted table automatically. I never know before hand how many orders have been placed for what products and what time frame. The tables need to be dynamic. One way of doing this is via the pivot table. But can I add columns that are not in the data sheet ? (column 7 & 9). Or do I need to add these to the data sheet ? Can i have formats added automatically in the pivot ? Can any of you tell me how I can do this dynamically ? What is the code that I need to write ? If there is something that is not clear do not hesitate to let me know. Your tips & recommendations are highly appreciated. Thanks in advance ! Gr, Mariam |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating dynamic (formatted) tables
Hi,
Thank you for your reply. However, importing data is not the problem, adding calculated columns isn't a problem either, i have that all in code and it works like a charm. The only problem I have is generating a number of tables automatically via code + a nice format, using the sheet (with the calculated columns) as my data sheet. Do i need to make a number of different little pivot tables with code ?? I have no idea so far. Gr, Mariam "sbell" wrote in message ... Mariam, Sounds like you are just trying to import data into Excel and than expand it. Just import it and add any columns you want. Use code for all of this. If the problem is that the number of records is constantly changing - just use variable ranges. I sprinkle the following throughout my code to get the number of rows (records): Dim lrow as Long lrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row ' this finds the last row with something in column A on Sheet(1) Range(Cells(1,1),Cells(lrow,5)).whatever or Range("A1:E" & lrow).whatever just change to suit your workbooks/sheets. hth -- steveb (Remove 'NoSpam' from email address if replying direct) "Mariam" wrote in message om... Dear all, I built a file that imports data from an external source (excel format). The code that I wrote does not only import the data but also adds additional calculated columns. One of the sheets contains data about orders to different countries, it looks something like this : Country Order_M Ord_Nr Qty Prod. TAT_act. SLA_TAT SLA_Achieved Austria jan. 04 prf001 25 pencil 12 13 yes Austria may. 04 prf002 36 paper 10 13 yes Belgium feb. 04 prf003 78 pencil 15 11 no France feb. 04 prf004 10 paper 25 11 no France mrt. 04 prf005 15 paper 35 11 no UK jan. 04 prf006 15 pencil 8 10 yes UK apr. 04 prf007 25 paper 9 10 yes Sweden apr. 04 prf008 30 paper 10 9 no In the next sheet (sheet 2) I want to make a number of tables using the above data. This table is formatted and looks as follows: |#orders |In TAT| | |OutTAT| Country| Month |Product |placed |Total | % |SLA |total | % |SLA| --------------------------------------------------------------------- Austria| jan. 04 |pencil | 1 | 1 |100%| 95% | 0 | 0 | 3%| | may. 04 |paper | 1 | 1 |100%| 95% | 0 | 0 | 3%| Austria total | 2 | 2 |100%| 95% | 0 | 0 | 3%| Column 1: Country name (column 1 in data sheet) Column 2: Month (Oder_Month in data sheet) Column 3: Product (Product in data sheet) Column 4: #Orders placed (Count Order_Nr in sheet 1 where country_sheet1 is equal to country_sheet2) Column 5: within TAT (Count "yes" in column SLA_Achieved in sheet 1 where country_sheet1 is equal to country_sheet2) Column 6: % (within TAT divided by #orders placed) Column 7: SLA pre-defined percentage (means that a minimum of 95% of all orders must be within TAT) Column 8: Outside TAT (Count "no" in column SLA_Achieved where country_sheet1 is equal to country_sheet2) Column 9: % (outside TAT divided by #orders placed) Column 10: SLA pre-defined percentage (means that a maximum of 3% of all orders can be outside TAT) I want to generate this formatted table automatically. I never know before hand how many orders have been placed for what products and what time frame. The tables need to be dynamic. One way of doing this is via the pivot table. But can I add columns that are not in the data sheet ? (column 7 & 9). Or do I need to add these to the data sheet ? Can i have formats added automatically in the pivot ? Can any of you tell me how I can do this dynamically ? What is the code that I need to write ? If there is something that is not clear do not hesitate to let me know. Your tips & recommendations are highly appreciated. Thanks in advance ! Gr, Mariam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with dynamic tables | Excel Worksheet Functions | |||
How can I get Pivot Tables to recognise time formatted data | Excel Discussion (Misc queries) | |||
auto creating an idex sheet when adding formatted sheets | Excel Discussion (Misc queries) | |||
Creating formatted Outlook Msg from VBA | Excel Programming | |||
Creating properly formatted text file from vbscript using excel data | Excel Programming |