Automatically generate formulas from just the row numbers (auto insert the column)

I don't even know how to search for this so I will explain my situation and hopefully there is a solution

I have an extensive spreadsheet which lists a number of variables for flaked stone surfaces, eg. quality, type, panel length, scar length, scar width etc.

These variable are recorded based on the individual scars as the rows, so my Master database looks something like this:

Site Name	Panel Number	Stone Quality	Panel Length	Flake Length (mm)	Flake Width (mm)
08-7BF01	1	3	3456.9	153.38	178.68
08-7BF01	1	3	3456.9	43.811	78.264
08-7BF01	1	3	3456.9	47.059	44.033
10-7BF03	5	3	175.38	37.091	24.872
10-7BF03	5	3	175.38	12.615	13.826
10-7BF03	5	3	175.38	13.826	19.305
10-7BF03	7	3	180.80	17.393	38.882
10-7BF03	7	3	180.80	24.033	24.187
11-7BF01	2	4	532.79	91.314	85.252

I have over 12 000 entries of this type



I am now wanting to produced a summary table which summaries this info by panel number. Example:

Site Name	Panel Number	Total Scars recorded	Stone Quality	Panel Length	Flake Length (longest)	Flake Length (shortest)	Flake Length (average)	Flake Length (median)	Flake Length (SD)	Flake Width (Longest)	Flake Width (shortest)	Flake Width (average)	Flake Width (median)	Flake Width (SD)
8-7BF01	1	127	4	3456.9	145.08	11.917	45.19	35.80	28.16	206.24	11.55	55.54	42.88	36.81
8-7BF01	2	65	2	1399.3	61.012	10.68	28.51	27.84	14.50	88.018	14.93	34.95	29.61	20.86
8-7BF01	6	85	3	1690.263	91.32	11.16	39.31	35.52	18.45	120.85	11.73	42.62	37.56	21.422
9-7BF02	1	4	3	192.14	49.56	29.90	39.45	38.88	9.84	100.83	58.93	80.351	81.30	20.96

There are over 1700 panel entries.



I have recorded the data ranges for the rows of each panel, eg. 8-7BF01 Panel 1 = 2:128, 8-7BF01 Panel 2 = 2:128, 129:193, 8-7BF01 Panel 6 = 206:290.

Currently I have a number of formulas for each row which I copy these data ranges. Below is an example of what I have to do for 8-7BF01 Panel 1.

Total scars =COUNT(G2:G128)
Stone Quality =AVERAGE(K2:K128)
Panel Length =AVERAGE(L:L128)
LxW =AVERAGE(Q2:Q128)
L:W =AVERAGE(R2:R128)
Flake Length (longest) =MAX(O2:O128)
Flake Length (shortest)=MIN(O2:O128)
Flake Length (average)=AVERAGE(O2:O128)
Flake Length (median)=MEDIAN(O2:O128)
Flake Length (SD)=STDEV(O2:O128)
Flake Width (longest) =MAX(P2:P128)
Flake Width (shortest)=MIN(P2:P128)
Flake Width (average)=AVERAGE(P2:P128)
Flake Width (median)=MEDIAN(P2:P128)
Flake Width (SD)=STDEV(P2:P128)

Plus some others which are more complicated
UF1% =COUNTIF(J2:J128,"UF1"), =X4/W3
UF2% =COUNTIF(J2:J128, "UF2"), =Y4/W3



I copy the data ranges for the panels into each formula and edit the column letter to correspond with the section of the data I am wanting to summaris. I then copy this into my summary spreadsheet.

I have to do this for each panel for up to 24 different summary data points. Doing this manually is understandably time-consuming. I am hoping there is a way to speed things up.

I know the formulas I am wanting to use and the row ranges for each panel.

Is there a way automatically populate with the necessary information?

Either by remembering the column info and linking in the data range or some other way?


Cheers

Also post a question on msoffice forum
http://www.msofficeforums.com/excel/...tml#post114148