It's a little inelegant, but use the following formula part where you have M:M:
INDIRECT(SUBSTITUTE(ADDRESS(1,10+ROW(),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,10+ROW(),4),"1",""))
and the reference will adjust as you go down rows.
It gets a cell address string based on the calculation you see that uses ROW(). Since "1" is used in the ADDRESS() function for the row number, the cell address string will always be for row 1 of whatever column the ROW() calculation generates. The "4" makes sure there are no dollar signs present (the address generated is "relative") so you only get the column and the row and since the row is always "1" SUBSTITUTE() can easily get rid of that leaving only the letter column reference from your calculation.
So you have a letter column reference without all the divide by 26 and MOD() this and that which you usually see for this kind of thing.
You need a range, so you do that twice and join the two pieces with &":" & in between, and then wrap them with INDIRECT() to make it a "real" reference, not just a string.
If it seems too much to put into the formula and still be able to udnerstand it, make a Named Range that makes sense to you and put the formula part above as its value, then use the Named Range in your sheetside formula.
You could use another extraction method to strip off the "1", for example:
=LEFT(ADDRESS(1,10+ROW(),4),LEN(ADDRESS(1,10+ROW(),4))-1)
but it's just gonna look ugly no matter what so...
The beauty of ADDRESS() is that it returns whatever is appropriate without a nightmare so "2522" for its column parameter returns "CTN" with no effort at all. Hard to argue with!
Oh... and I don't see what the point of just about any of the "*" characters is (except for one, just one). Not even gonna guess about that. Ditch them is my thought (except for the one).
