Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 37
Default Transposing in formula

I have a formula that I need to copy along a row. Part of the formula refers
to cells that are elsewhere in a column. Is there a way of copying the
formula along the row without having to then manually change the references
for the data in the column.
  #2   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Transposing in formula

Need more detail. Post your start cell formula, and describe what you want to
happen when you copy it across.

Meantime, as an example of a dynamic transpose using OFFSET ..
In Sheet2,
you can place this in any starting cell, say, in B2:
=OFFSET(Sheet1!$A$1,COLUMNS($A:A)-1,)
When you copy B2 across (ie "rowwise"), it'll return the "columnwise"
contents from Sheet1's A1, A2, A3, etc (viz it'll return a dynamic transpose
of Sheet1's col A)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Ken G." wrote:
I have a formula that I need to copy along a row. Part of the formula refers
to cells that are elsewhere in a column. Is there a way of copying the
formula along the row without having to then manually change the references
for the data in the column.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 37
Default Transposing in formula


OK. Simplified, the formula in say A1 is =$A$10+ A11
Data exists in column A in cells A11....A15

When I copy the formula in A1 across B1, C1, D1 & E1 it will become
$A$10+B11, $A$10+B12, $A$10+B13 etc. whereas I want it to be $A$10+A11,
$A$10+A12 etc.


"Max" wrote:

Need more detail. Post your start cell formula, and describe what you want to
happen when you copy it across.

Meantime, as an example of a dynamic transpose using OFFSET ..
In Sheet2,
you can place this in any starting cell, say, in B2:
=OFFSET(Sheet1!$A$1,COLUMNS($A:A)-1,)
When you copy B2 across (ie "rowwise"), it'll return the "columnwise"
contents from Sheet1's A1, A2, A3, etc (viz it'll return a dynamic transpose
of Sheet1's col A)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Ken G." wrote:
I have a formula that I need to copy along a row. Part of the formula refers
to cells that are elsewhere in a column. Is there a way of copying the
formula along the row without having to then manually change the references
for the data in the column.

  #4   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Transposing in formula

Ok, you could replace the formula in A1 with this:
=$A$10+OFFSET($A$10,COLUMNS($A:A),)
Copy A1 across, and you'd the required results

Take a moment to press the "Yes" button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"Ken G." wrote:
OK. Simplified, the formula in say A1 is =$A$10+ A11
Data exists in column A in cells A11....A15

When I copy the formula in A1 across B1, C1, D1 & E1 it will become
$A$10+B11, $A$10+B12, $A$10+B13 etc. whereas I want it to be $A$10+A11,
$A$10+A12 etc.


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
Transposing Nanapat Excel Discussion (Misc queries) 2 April 2nd 08 11:38 PM
Transposing Cells which contains formula Hemant Excel Worksheet Functions 2 November 24th 06 02:34 PM
Transposing Deena Excel Worksheet Functions 2 October 27th 06 05:13 PM
TRANSPOSING GARY Excel Discussion (Misc queries) 1 March 17th 06 12:09 PM
Transposing cells GARY Excel Discussion (Misc queries) 0 March 17th 06 01:14 AM


All times are GMT +1. The time now is 10:57 AM.

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"