Dynamically creating formatted tables from raw data

[Taylor]

Inspired by Peltiers wonderful Dynamic charting tutorial
<http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html>, I
started to wonder how feasible it would be to create a set of rules
that would allow me to dynamically create a table based on exported
data of variable row count, column count, etc. I'm not really 'fishing
for code' here, as much as fishing for insights on how this would play
to automation/excel's strengths and weaknesses.


By table, I mean a presentable table ultimately destined for inclusion
in a printed report - thin borders for individual cells, thicker
borders denoting groups of information, merged centered column headers,
etc.

Currently I export the results of a crosstab query from Access 2002
onto an Excel 2002 worksheet, let's call it "SourceData". Another
worksheet ("Tbl4Export") contains formatted tables linked to the data
on "Sourcedata."

The data are very variable - It involves schools surveyed over the span
of several years, broken down by grade.

A typical table might look like (this is a stripped down version, so
text wrapping doesn't bone me; it could have up to 4 grades)

"Sch" = School Data
"St" = State Data
02 = Year 2002 etc.
Var = Variable
Pretending A1:I1, A7:I7 has an uninterupted border.


A B C D E F G H I
______________________________________________________
1|_Section Description 1_______________________________|
2| | Grade 6 | Grade 7 |
3| |Sch02|Sch03|Sch04|St 04|Sch02|Sch03|Sch04|St 04|
4|Var1 | 6.0| 6.5| 7.5| 7.2| 8.0| 7.5| 7.6| 9.2|
5|Var2 | 4.0| 3.5| 4.5| 5.2| 5.0| 6.5| 5.6| 7.2|
6|_____|_______________________|_______________________|
7|_Section Description 2_______________________________|
8| | Grade 6 | Grade 7 |
9| |Sch02|Sch03|Sch04|St 04|Sch02|Sch03|Sch04|St 04|
10|Var3 | 6.0| 6.5| 7.5| 7.2| 8.0| 7.5| 7.6| 9.2|
11|Var4 | 4.0| 3.5| 4.5| 5.2| 5.0| 6.5| 5.6| 7.2|
12|_____|_______________________|_______________________|

But say the School has missed a couple of years in the survey or didn't
do all grades one year, and also had an extra variable they were
measuring:

A B C D E F G H I
______________________________________________________
1|_Section Description 1_______________________________|
2| | Grade 6 | Grade 7 | Grade 9 |
3| |Sch03|Sch04|St 04|Sch02|Sch04|St 04|Sch04|St 04|
4|Var1 | 6.0| 7.5| 7.2| 8.0| 7.5| 7.6| 9.2| 9.3|
5|Var2 | 4.0| 4.5| 5.2| 5.0| 6.5| 5.6| 7.2| 8.1|
6|_____|_________________|_________________|___________|
7|_Section Description 2_______________________________|
8| | Grade 6 | Grade 7 | Grade 9 |
9| |Sch03|Sch04|St 04|Sch02|Sch04|St 04|Sch04|St 04|
10|Var3 | 6.0| 6.5| 7.5| 7.2| 8.0| 7.5| 7.6| 9.2|
11|Var4 | 4.0*| 3.5| 4.5| 5.2| 5.0| 6.5| 5.6| 7.2|
12|Var5 | | 1.5| 2.5| n/a | 6.0| 6.9| 3.2| 5.2|
13|_____|_________________|_________________|___________|
14|*Data categories var4 and var5 combined in year 2003 |
15|_____________________________________________________|



~On the Sourcedata sheet, the original data looks like this:

A B C D E F G H I J
1 Sec1 Section Description 1
2 grLbl Grade6Grade6Grade6Grade7Grade7Grade7Grade9Grade9
3 SchYr Sch03 Sch04 St 04 Sch02 Sch04 St 04 Sch04 St 04
4 v1 Var1 6.0 7.5 7.2 8.0 7.5 7.6 9.2 9.3
5 v2 Var2 4.0 4.5 5.2 5.0 6.5 5.6 7.2 8.1
6 Spacer
7 Sec2 Section Description 2

8 grLbl Grade6Grade6Grade6Grade7Grade7Grade7Grade9Grade9
9 SchYr Sch03 Sch04 St 04 Sch02 Sch04 St 04 Sch04 St 04
10 v3 Var3 6.0 6.5 7.5 7.2 8.0 7.5 7.6 9.2
11 v4 Var4 4.0* 3.5 4.5 5.2 5.0 6.5 5.6 7.2
12 v5 Var5 1.5 2.5 n/a 6.0 6.9 3.2 5.2
13 Spacer
14 v45lbl *Data categories var4 and var5 combined in year 2003

Note that in column A, I have some variable codes that can be used to
help differentiate/demarcate sections (i.e. "grLbl" indicates that row
contains grade labels).

Are there some elegant, easily adaptable approaches to "dynamic
tables"?

I'd love to put together a set of rules in VBA to programatically
address stuff like (a hypothetical list):
A)merge/center-across-selection the grade columns, apply heavy outline
to grade section borders, as appropriate (how to detect what defines a
"section"? Identical cell values?)
B}Make Section description rows grey, while
B)Always make the state data column yellow
C)Conditionally center some data (i.e. the "n/a" for variable 5 in
2002)
D)Separate each sections by a heavy border (i.e. on the second example,
the 6th grade data would have heavy border around ranges B22, B26,
B88, B813)
E)Adjust column width/row heighth to compensate for charts with fewer
columns (so sizes still mesh nicely with predetermined format in word?)
F)Be easily adjustable for the formatting whims of different schools?

I have so far had a lot of luck hiding table rows and columns via VBA,
but have gotten frustrated with things like disappearing cell borders
(oh, so A1 had a right border, but B1 didn't have a left border), the
irrational logic of: hiding columns + adjacent merged sections =
kablooey, the ease of adding new columns/rows with correct formatting
already in place, changing color schemes without individually selecting
different sections.


Wow. As Gramdpa Simpson said, "Anyway, long story short... is a phrase
whose origins are complicated and rambling..."

I realize I am asking about 20 different specific questions, but I'm
really not trying to get y'all to do my work for me. Mainly I'd love
tips on how to better understand my question, links to folks that have
discussed this before, the order I should consider tackling problems
("Do the borders last."), code snippets as a guide (I'm fairly code
savvy), grandiose yet vague theories...

Thanks,

Taylor Bryant

 

[Jim Thomlinson]

Have you considered a pivot table linked directly to the Access database.
There are a bunch of auto format options included and you can group on your
dates to get time frames and a whole pile of other goodies to work with...
including charting. All at the press of a refresh button. If you are familiar
with Cross Tabs then pivot tables should be very straight forward (only in my
opinion a whole lot more useful).