I need help figuring out how to copy a formula horzontally on one tab while
the source data resides on a different tab vertically. Can't use copy/paste
special/transpose, because the links have to be dynamic. Gord Dibben
suggested using the INDEX function. But this only worked when I was working
on the extreme left column. However, if I try to reference a column, say
column F, which is 5 columns over the formula starts looking at the 5th data
point down in the column. So If I want to start the formula in F:1, it will
actually start in F:5. Is there a way to change that? Or is there another
function that would do the job better?
Thanks Everyone.

Just a thought, you could transpose the data on the other sheet (that is
currently in a column)

"Shannon" wrote:

I need help figuring out how to copy a formula horzontally on one tab while
the source data resides on a different tab vertically. Can't use copy/paste
special/transpose, because the links have to be dynamic. Gord Dibben
suggested using the INDEX function. But this only worked when I was working
on the extreme left column. However, if I try to reference a column, say
column F, which is 5 columns over the formula starts looking at the 5th data
point down in the column. So If I want to start the formula in F:1, it will
actually start in F:5. Is there a way to change that? Or is there another
function that would do the job better?
Thanks Everyone.

Shannon

If data is is Column F on source sheet and you want it transposed to target
sheet at F1 and across use this amended formula.

=INDEX(Sheet6!$F:$F,COLUMN()-5)

You gotta play around with the column X:X and Columns()-whatever offset.

Alternative is a macro that cuts and transposes formulas.


Sub Transpose_Formulas()
Dim sRange As Range, dCell As Range
Dim sCell As Range, i As Integer, j As Integer
Dim str As String
'get input ranges. default box is filled by use of text
'variable set to the selected address
str = Selection.Address(False, False)
Application.ScreenUpdating = True
On Error Resume Next
Set sRange = Application.InputBox(prompt:= _
"Select the range of cells to be transposed." & Chr(10) & Chr(10) _
& "If cells do not have Formulas, Sub will end!.", Type:=8, _
default:=str)
If Not sRange.HasFormula Then
MsgBox "Cells do not contain formulas"
End
Else
If sRange.HasFormula Then
Set dCell = Application.InputBox(prompt:= _
"Select the top left cell of the output location.", _
Type:=8)
If dCell Is Nothing Then End
On Error GoTo 0
'set single cell references for use in the next step
Set sCell = sRange.Cells(1, 1)
Set dCell = dCell.Cells(1, 1)
'loop through all cells, working backward to the top left cell
For i = sRange.Rows.Count - 1 To 0 Step -1
For j = sRange.Columns.Count - 1 To 0 Step -1
If i 0 Or j 0 Then
'do this for all but the first cell
sCell.Offset(i, j).Cut _
Destination:=dCell.Offset(j, i)
Else
'do top corner last. Otherwise references are changed
sCell.Cut Destination:=dCell
End If
Next j
Next i
End If
End If
End Sub


Gord

On Wed, 21 Dec 2005 09:21:03 -0800, "Shannon"
wrote:

I need help figuring out how to copy a formula horzontally on one tab while
the source data resides on a different tab vertically. Can't use copy/paste
special/transpose, because the links have to be dynamic. Gord Dibben
suggested using the INDEX function. But this only worked when I was working
on the extreme left column. However, if I try to reference a column, say
column F, which is 5 columns over the formula starts looking at the 5th data
point down in the column. So If I want to start the formula in F:1, it will
actually start in F:5. Is there a way to change that? Or is there another
function that would do the job better?
Thanks Everyone.

Try this:

=INDEX(Sheet1!$F:$F,COLUMNS($A:A))

This can be entered *anywhere*, and copied to the right, and return your
Column F values from Sheet1.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Shannon" wrote in message
...
I need help figuring out how to copy a formula horzontally on one tab
while
the source data resides on a different tab vertically. Can't use
copy/paste
special/transpose, because the links have to be dynamic. Gord Dibben
suggested using the INDEX function. But this only worked when I was
working
on the extreme left column. However, if I try to reference a column, say
column F, which is 5 columns over the formula starts looking at the 5th
data
point down in the column. So If I want to start the formula in F:1, it
will
actually start in F:5. Is there a way to change that? Or is there
another
function that would do the job better?
Thanks Everyone.

Works like a charm. Gord, You da Man!!!!!

"Gord Dibben" wrote:

Shannon

If data is is Column F on source sheet and you want it transposed to target
sheet at F1 and across use this amended formula.

=INDEX(Sheet6!$F:$F,COLUMN()-5)

You gotta play around with the column X:X and Columns()-whatever offset.

Alternative is a macro that cuts and transposes formulas.


Sub Transpose_Formulas()
Dim sRange As Range, dCell As Range
Dim sCell As Range, i As Integer, j As Integer
Dim str As String
'get input ranges. default box is filled by use of text
'variable set to the selected address
str = Selection.Address(False, False)
Application.ScreenUpdating = True
On Error Resume Next
Set sRange = Application.InputBox(prompt:= _
"Select the range of cells to be transposed." & Chr(10) & Chr(10) _
& "If cells do not have Formulas, Sub will end!.", Type:=8, _
default:=str)
If Not sRange.HasFormula Then
MsgBox "Cells do not contain formulas"
End
Else
If sRange.HasFormula Then
Set dCell = Application.InputBox(prompt:= _
"Select the top left cell of the output location.", _
Type:=8)
If dCell Is Nothing Then End
On Error GoTo 0
'set single cell references for use in the next step
Set sCell = sRange.Cells(1, 1)
Set dCell = dCell.Cells(1, 1)
'loop through all cells, working backward to the top left cell
For i = sRange.Rows.Count - 1 To 0 Step -1
For j = sRange.Columns.Count - 1 To 0 Step -1
If i 0 Or j 0 Then
'do this for all but the first cell
sCell.Offset(i, j).Cut _
Destination:=dCell.Offset(j, i)
Else
'do top corner last. Otherwise references are changed
sCell.Cut Destination:=dCell
End If
Next j
Next i
End If
End If
End Sub


Gord

On Wed, 21 Dec 2005 09:21:03 -0800, "Shannon"
wrote:

I need help figuring out how to copy a formula horzontally on one tab while
the source data resides on a different tab vertically. Can't use copy/paste
special/transpose, because the links have to be dynamic. Gord Dibben
suggested using the INDEX function. But this only worked when I was working
on the extreme left column. However, if I try to reference a column, say
column F, which is 5 columns over the formula starts looking at the 5th data
point down in the column. So If I want to start the formula in F:1, it will
actually start in F:5. Is there a way to change that? Or is there another
function that would do the job better?
Thanks Everyone.

Shannon, here is a bit from RagDyer which showed up in another thread.

Try this:

=INDEX(Sheet1!$F:$F,COLUMNS($A:A))

This can be entered *anywhere*, and copied to the right, and return your
Column F values from Sheet1.
--
HTH,

RD


Gord

On Wed, 21 Dec 2005 13:08:02 -0800, "Shannon"
wrote:

Works like a charm. Gord, You da Man!!!!!

"Gord Dibben" wrote:

Shannon

If data is is Column F on source sheet and you want it transposed to target
sheet at F1 and across use this amended formula.

=INDEX(Sheet6!$F:$F,COLUMN()-5)

You gotta play around with the column X:X and Columns()-whatever offset.

Alternative is a macro that cuts and transposes formulas.


Sub Transpose_Formulas()
Dim sRange As Range, dCell As Range
Dim sCell As Range, i As Integer, j As Integer
Dim str As String
'get input ranges. default box is filled by use of text
'variable set to the selected address
str = Selection.Address(False, False)
Application.ScreenUpdating = True
On Error Resume Next
Set sRange = Application.InputBox(prompt:= _
"Select the range of cells to be transposed." & Chr(10) & Chr(10) _
& "If cells do not have Formulas, Sub will end!.", Type:=8, _
default:=str)
If Not sRange.HasFormula Then
MsgBox "Cells do not contain formulas"
End
Else
If sRange.HasFormula Then
Set dCell = Application.InputBox(prompt:= _
"Select the top left cell of the output location.", _
Type:=8)
If dCell Is Nothing Then End
On Error GoTo 0
'set single cell references for use in the next step
Set sCell = sRange.Cells(1, 1)
Set dCell = dCell.Cells(1, 1)
'loop through all cells, working backward to the top left cell
For i = sRange.Rows.Count - 1 To 0 Step -1
For j = sRange.Columns.Count - 1 To 0 Step -1
If i 0 Or j 0 Then
'do this for all but the first cell
sCell.Offset(i, j).Cut _
Destination:=dCell.Offset(j, i)
Else
'do top corner last. Otherwise references are changed
sCell.Cut Destination:=dCell
End If
Next j
Next i
End If
End If
End Sub


Gord

On Wed, 21 Dec 2005 09:21:03 -0800, "Shannon"
wrote:

I need help figuring out how to copy a formula horzontally on one tab while
the source data resides on a different tab vertically. Can't use copy/paste
special/transpose, because the links have to be dynamic. Gord Dibben
suggested using the INDEX function. But this only worked when I was working
on the extreme left column. However, if I try to reference a column, say
column F, which is 5 columns over the formula starts looking at the 5th data
point down in the column. So If I want to start the formula in F:1, it will
actually start in F:5. Is there a way to change that? Or is there another
function that would do the job better?
Thanks Everyone.