Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Shannon
 
Posts: n/a
Default Returned: Copying a formula horizontally, the source data is verti

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
JR
 
Posts: n/a
Default Returned: Copying a formula horizontally, the source data is verti

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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
Gord Dibben
 
Posts: n/a
Default Returned: Copying a formula horizontally, the source data is verti

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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
RagDyer
 
Posts: n/a
Default Returned: Copying a formula horizontally, the source data is verti

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.


  #5   Report Post  
Posted to microsoft.public.excel.misc
Shannon
 
Posts: n/a
Default Returned: Copying a formula horizontally, the source data is v

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.




  #6   Report Post  
Posted to microsoft.public.excel.misc
Gord Dibben
 
Posts: n/a
Default Returned: Copying a formula horizontally, the source data is v

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can you extend a formula horizontally while the data is vertical Shannon Excel Discussion (Misc queries) 3 December 20th 05 01:46 AM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
Copying data from formula output robin m Excel Discussion (Misc queries) 2 August 30th 05 06:04 PM
Using a relative SHEET reference for source data in a chart James Charts and Charting in Excel 6 August 16th 05 05:07 PM


All times are GMT +1. The time now is 09:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"