Creating dynamic (formatted) tables

[Mariam]

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

 

[sbell]

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

 

[Mariam]

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