There is some basic principle that I am not really grasping about named cells.

I get cell addresses in many ways for instance:

=ADDRESS(AGGREGATE(14, 6, ROW(C$3:C2000)/(C$3:C2000<>""), 1),COLUMN(C1))
This instance returns the last non blank cell address in Col C.

Wrap it in a INDIRECT and it will return the cell value.

So how the hell do i make a NAME so i can use in say =SUM(C1:NAME)
?

It would be great if you could elaborate on why and how because to me at least it seems the names take references slightly differently than regular formulas (If i just threw a concatenation formula takes the reference and works)