Transpose formula's ??

[VegasNath]

Is it possible to drag a formula across columns, transposing the original data? EG:

In cell A1
=Detail!A1
... drag to E1, with the results A1:A5

[mbarron]

You can use the OFFSET function:
=OFFSET(Details!$A$1,COLUMN()-1,0)

or the INDEX function:
=INDEX(Details!$A:$A,COLUMN())

[HansV]

Select A1:E1.
Enter the formula

=TRANSPOSE(Detail!A1:A5)

Press Ctrl+Shift+Enter to save it as an array formula.

[VegasNath]

The offset does the trick, Thanks Mike.

Hans: I need to be able to copy the formula across columns, so the Transpose array will not work for me. Thanks.

[VegasNath]

The offset does the trick, Thanks Mike.

My only problem with this is that every formula across the column is identical, but all produce different results (as expected). It works, but I don't understand it. It's difficult to understand the audit trail.

[HansV]

COLUMN() returns the column number of the cell containing the formula: 1 for column A, 2 for column B, etc.
COLUMN()-1 is 1 less than the column number.
OFFSET(Details!$A$1,COLUMN()-1,0) starts at A1 on Detail(s) and moves COLUMN()-1 rows down, 0 columns to the right. So the result depends on the column number of the cell containing the formula.

[VegasNath]

COLUMN() returns the column number of the cell containing the formula: 1 for column A, 2 for column B, etc.
COLUMN()-1 is 1 less than the column number.
OFFSET(Details!$A$1,COLUMN()-1,0) starts at A1 on Detail(s) and moves COLUMN()-1 rows down, 0 columns to the right. So the result depends on the column number of the cell containing the formula.

Thankyou for the explanation Hans, I think I understand it, even if I still find it a little confusing...

How can I eliminate the #VALUE! from this?

=(OFFSET('5.19'!$V4,COLUMN()-3,0)/Unit)+(OFFSET('5.19'!$W4,COLUMN()-3,0)/Unit)

something along the lines of...

=IF(ISERROR(OFFSET('5.19'!$V4,COLUMN()-3,0)/Unit)+(OFFSET('5.19'!$W4,COLUMN()-3,0)/Unit)=TRUE,0,OFFSET('5.19'!$V4,COLUMN()-3,0)/Unit)+(OFFSET('5.19'!$W4,COLUMN()-3,0)/Unit)

I am trying to sum columns V&W, dragging the formula accross columns, which will alter to the relevant row from the source sheet. Some of which will populate #VALUE! which I want to ignore. Hope this makes sense. Thanks

[HansV]

Try

=IF(ISERROR(OFFSET('5.19'!$V4,COLUMN()-3,0)+OFFSET('5.19'!$W4,COLUMN()-3,0)),0,(OFFSET('5.19'!$V4,COLUMN()-3,0)+OFFSET('5.19'!$W4,COLUMN()-3,0))/Unit)

In Excel 2007 or later, you can use

=IFERROR((OFFSET('5.19'!$V4,COLUMN()-3,0)+OFFSET('5.19'!$W4,COLUMN()-3,0))/Unit,0)

[VegasNath]

Thanks.

The 2007 version is a vast improvement!