I am trying to copy an entire row from j9:sw9 and transpose the information to column so that it would reference that row starting at j9 being in say a1600 then k9 in a1601 L9 in a1602 etc. but I need them to reference the cell so that when I change the value in the row it will automatically show that in the column. Is there any way to do this? I tried to start by putting j9 in a1600 then k9 in a1601 and drag it down but excel wont follow that sequence to be the same as its column headings. Another option that would be much easier but I havent found a way to do it would be to have a way that sums every nth cell in the row from j9:sw9 without me having to click into the cell where the total is. Right now to sum the row I have:

Function SumEveryNth2007(rRange As Range, lNth As Long, Optional SumEveryNthRow As Boolean) As Single
Dim rCell As Range
Dim strAddress As String
If SumEveryNthRow = False Then
With WorksheetFunction
For Each rCell In rRange
lStep = lStep + 1
If lStep Mod lNth = 0 Then sTot = .Sum(rCell, sTot)
Next rCell
End With

SumEveryNth2007 = sTot
Else
strAddress = rRange.Address
SumEveryNth2007 = Evaluate("=SUMPRODUCT((MOD(ROW(" & strAddress & ")," & lNth & ")=0)*(" & strAddress & "))")
End If

End Function

with this in the cell to have the total:

=SumEveryNth2007($I9:$SW9,2)

This works but I have to click up in the formula bar like I am going to edit the formula each time I want to have it sum what I have entered. Is there a way to make it constantly recalculate? There are not a lot of rows so I am not worried about it being slow I just want it to sum every 2nd cell in j9:sw9. If there is no way to do it I want to have a column reference the row as I mentioned first and just use this array formula:

{=SUM(IF(MOD(ROW($A$1600:$A$2074)-ROW($A$1600),$A$1599)=0,$A$1600:$A$2074,0))} where $A$1599=2 to sum every 2nd row