Filling (Across) referencing Excel table without re-typing: is this possible?

[rodwhiteley]

Hey,
I have a table with 44 columns and a couple of thousand rows I have to do a bunch of array formulae on to calculate standard deviations, median, min, max, IQR, count, count >0, and a few other things.
The summary tables (for the 44 columns) each have 10 bins that I will copy the formula down (being the deciles (0 to 9) for each of these values).

All this is fine, except copying to the right copies with the table nomenclature intact, so instead of the formula filling to the right with the ranges, I have a replication of the table nomenclature. ie TableName[column1] when you copy-Right stays TableName[column1] rather then becoming TableName[Column2]
for example, one cell's column which will fill down fine reads:
={STDEV(IF(DailyAssessmentData[Decile]=$B15,DailyAssessmentData[AveragePain]))}
When I fill that right in the final range the table nomenclature ([AveragePain]) copies to the adjacent column, and I have to go and manually change this value before filling down the column. This is tedious for the 44 rows, and 12 tables.
One solution would be to change the range from table nomenclature to cell referencing (in this case [AveragePain] is F$2:F$2146) however for error checking and later readability/verification purposes I really like having the table column names in the formulae.

Is it possible to write a formula referencing a table, using the table nomenclature, that will fill across? Perhaps can I use some sort of TEXT function to reference the column header and place this within the formula?

Thanks for any help,
Rod

 

[pgc01]

All this is fine, except copying to the right copies with the table nomenclature intact, so instead of the formula filling to the right with the ranges, I have a replication of the table nomenclature. ie TableName[column1] when you copy-Right stays TableName[column1] rather then becoming TableName[Column2]

Hi Rod

That is right if you copy it, but if you instead drag it to the right you'll see that it becomes TableName[Column2].

So write the formulas and then drag them to the right.