software

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)

quot;Shannonquot; wrote:

gt; I need help figuring out how to copy a formula horzontally on one tab while
gt; the source data resides on a different tab vertically. Can't use copy/paste
gt; special/transpose, because the links have to be dynamic. Gord Dibben
gt; suggested using the INDEX function. But this only worked when I was working
gt; on the extreme left column. However, if I try to reference a column, say
gt; column F, which is 5 columns over the formula starts looking at the 5th data
gt; point down in the column. So If I want to start the formula in F:1, it will
gt; actually start in F:5. Is there a way to change that? Or is there another
gt; function that would do the job better?
gt; 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:= _
quot;Select the range of cells to be transposed.quot; amp; Chr(10) amp; Chr(10) _
amp; quot;If cells do not have Formulas, Sub will end!.quot;, Type:=8, _
default:=str)
If Not sRange.HasFormula Then
MsgBox quot;Cells do not contain formulasquot;
End
Else
If sRange.HasFormula Then
Set dCell = Application.InputBox(prompt:= _
quot;Select the top left cell of the output location.quot;, _
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 gt; 0 Or j gt; 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 SubGord

On Wed, 21 Dec 2005 09:21:03 -0800, quot;Shannonquot;
gt; wrote:

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

quot;Gord Dibbenquot; wrote:

gt; Shannon
gt;
gt; If data is is Column F on source sheet and you want it transposed to target
gt; sheet at F1 and across use this amended formula.
gt;
gt; =INDEX(Sheet6!$F:$F,COLUMN()-5)
gt;
gt; You gotta play around with the column X:X and Columns()-whatever offset.
gt;
gt; Alternative is a macro that cuts and transposes formulas.
gt;
gt;
gt; Sub Transpose_Formulas()
gt; Dim sRange As Range, dCell As Range
gt; Dim sCell As Range, i As Integer, j As Integer
gt; Dim str As String
gt; 'get input ranges. default box is filled by use of text
gt; 'variable set to the selected address
gt; str = Selection.Address(False, False)
gt; Application.ScreenUpdating = True
gt; On Error Resume Next
gt; Set sRange = Application.InputBox(prompt:= _
gt; quot;Select the range of cells to be transposed.quot; amp; Chr(10) amp; Chr(10) _
gt; amp; quot;If cells do not have Formulas, Sub will end!.quot;, Type:=8, _
gt; default:=str)
gt; If Not sRange.HasFormula Then
gt; MsgBox quot;Cells do not contain formulasquot;
gt; End
gt; Else
gt; If sRange.HasFormula Then
gt; Set dCell = Application.InputBox(prompt:= _
gt; quot;Select the top left cell of the output location.quot;, _
gt; Type:=8)
gt; If dCell Is Nothing Then End
gt; On Error GoTo 0
gt; 'set single cell references for use in the next step
gt; Set sCell = sRange.Cells(1, 1)
gt; Set dCell = dCell.Cells(1, 1)
gt; 'loop through all cells, working backward to the top left cell
gt; For i = sRange.Rows.Count - 1 To 0 Step -1
gt; For j = sRange.Columns.Count - 1 To 0 Step -1
gt; If i gt; 0 Or j gt; 0 Then
gt; 'do this for all but the first cell
gt; sCell.Offset(i, j).Cut _
gt; Destination:=dCell.Offset(j, i)
gt; Else
gt; 'do top corner last. Otherwise references are changed
gt; sCell.Cut Destination:=dCell
gt; End If
gt; Next j
gt; Next i
gt; End If
gt; End If
gt; End Sub
gt;
gt;
gt; Gord
gt;
gt; On Wed, 21 Dec 2005 09:21:03 -0800, quot;Shannonquot;
gt; gt; wrote:
gt;
gt; gt;I need help figuring out how to copy a formula horzontally on one tab while
gt; gt;the source data resides on a different tab vertically. Can't use copy/paste
gt; gt;special/transpose, because the links have to be dynamic. Gord Dibben
gt; gt;suggested using the INDEX function. But this only worked when I was working
gt; gt;on the extreme left column. However, if I try to reference a column, say
gt; gt;column F, which is 5 columns over the formula starts looking at the 5th data
gt; gt;point down in the column. So If I want to start the formula in F:1, it will
gt; gt;actually start in F:5. Is there a way to change that? Or is there another
gt; gt;function that would do the job better?
gt; gt;Thanks Everyone.
gt;

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,

RDGord

On Wed, 21 Dec 2005 13:08:02 -0800, quot;Shannonquot;
gt; wrote:

gt;Works like a charm. Gord, You da Man!!!!!
gt;
gt;quot;Gord Dibbenquot; wrote:
gt;
gt;gt; Shannon
gt;gt;
gt;gt; If data is is Column F on source sheet and you want it transposed to target
gt;gt; sheet at F1 and across use this amended formula.
gt;gt;
gt;gt; =INDEX(Sheet6!$F:$F,COLUMN()-5)
gt;gt;
gt;gt; You gotta play around with the column X:X and Columns()-whatever offset.
gt;gt;
gt;gt; Alternative is a macro that cuts and transposes formulas.
gt;gt;
gt;gt;
gt;gt; Sub Transpose_Formulas()
gt;gt; Dim sRange As Range, dCell As Range
gt;gt; Dim sCell As Range, i As Integer, j As Integer
gt;gt; Dim str As String
gt;gt; 'get input ranges. default box is filled by use of text
gt;gt; 'variable set to the selected address
gt;gt; str = Selection.Address(False, False)
gt;gt; Application.ScreenUpdating = True
gt;gt; On Error Resume Next
gt;gt; Set sRange = Application.InputBox(prompt:= _
gt;gt; quot;Select the range of cells to be transposed.quot; amp; Chr(10) amp; Chr(10) _
gt;gt; amp; quot;If cells do not have Formulas, Sub will end!.quot;, Type:=8, _
gt;gt; default:=str)
gt;gt; If Not sRange.HasFormula Then
gt;gt; MsgBox quot;Cells do not contain formulasquot;
gt;gt; End
gt;gt; Else
gt;gt; If sRange.HasFormula Then
gt;gt; Set dCell = Application.InputBox(prompt:= _
gt;gt; quot;Select the top left cell of the output location.quot;, _
gt;gt; Type:=8)
gt;gt; If dCell Is Nothing Then End
gt;gt; On Error GoTo 0
gt;gt; 'set single cell references for use in the next step
gt;gt; Set sCell = sRange.Cells(1, 1)
gt;gt; Set dCell = dCell.Cells(1, 1)
gt;gt; 'loop through all cells, working backward to the top left cell
gt;gt; For i = sRange.Rows.Count - 1 To 0 Step -1
gt;gt; For j = sRange.Columns.Count - 1 To 0 Step -1
gt;gt; If i gt; 0 Or j gt; 0 Then
gt;gt; 'do this for all but the first cell
gt;gt; sCell.Offset(i, j).Cut _
gt;gt; Destination:=dCell.Offset(j, i)
gt;gt; Else
gt;gt; 'do top corner last. Otherwise references are changed
gt;gt; sCell.Cut Destination:=dCell
gt;gt; End If
gt;gt; Next j
gt;gt; Next i
gt;gt; End If
gt;gt; End If
gt;gt; End Sub
gt;gt;
gt;gt;
gt;gt; Gord
gt;gt;
gt;gt; On Wed, 21 Dec 2005 09:21:03 -0800, quot;Shannonquot;
gt;gt; gt; wrote:
gt;gt;
gt;gt; gt;I need help figuring out how to copy a formula horzontally on one tab while
gt;gt; gt;the source data resides on a different tab vertically. Can't use copy/paste
gt;gt; gt;special/transpose, because the links have to be dynamic. Gord Dibben
gt;gt; gt;suggested using the INDEX function. But this only worked when I was working
gt;gt; gt;on the extreme left column. However, if I try to reference a column, say
gt;gt; gt;column F, which is 5 columns over the formula starts looking at the 5th data
gt;gt; gt;point down in the column. So If I want to start the formula in F:1, it will
gt;gt; gt;actually start in F:5. Is there a way to change that? Or is there another
gt;gt; gt;function that would do the job better?
gt;gt; gt;Thanks Everyone.
gt;gt;