Automatically transpose the data in sheet 1 rows into columns in sheet 2.

Will someone please help me on how to automatically transfer the data in sheet 1 (which are in rows) into columns in sheet 2.

I have done this using a VBA, but what I need is to do this without the use of a VBA. Is it even possible?
Also, I don't want to use the formula: =INDIRECT("Sheet1!"&ADDRESS(1,ROW())) because I have to put it manually on each cell.

What I want to happen is, once you input something in sheet 1, it automatically transpose the data into columns in sheet 2.

like this for example,
this is what the sheet 1 looks like:
1.JPG

then it will be displayed in sheet 2 like this:
2.JPG


Please help me on this. Advance thank you.

Hi,

Goto Sheet2 and select A1:C5, press F2, and input this array formula :

Formula:

=TRANSPOSE(Sheet1!A1:E3)

and end this by hold and press Ctrl Shift Enter at the same time (because this is an array formula, not a regular formula)

Regards

Here's a non-array formula in Sheet2 A1:

Formula:

=IFERROR(INDEX(Sheet1!$A$1:$E$3,COLUMNS($A:A),ROWS($1:1)),"")

then fill down 5 rows and across 3 columns.

Is this what you meant?

Originally Posted by Azalea11;
....................
[B

@FlameRetired[/B], if you worked one out please do upload it. Thank you.

OK. But did you see what nflsales uploaded. The formula in that file takes care of it. Mine is a bit clumsy. It has two dynamic named ranges: one if the

last row of source data is numeric and another one if text. They are swappable.

In Name Manager you'll find these:

Formula:

SourceDatNum	=(Sheet1!$A$1:INDEX(Sheet1!$1:$1,MATCH("zzzzzzz",Sheet1!$1:$1,1)))Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(1E+306,Sheet1!$A:$A,1)))

SourceDatText	=(Sheet1!$A$1:INDEX(Sheet1!$1:$1,MATCH("zzzzzzz",Sheet1!$1:$1,1)))Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("zzzzzzzzz",Sheet1!$A:$A,1)))

In Sheet2 A1 this for numeric:

Formula:

=IFERROR(INDEX(SourceDatNum,COLUMNS($A:A),ROWS($1:1)),"")

This one for text:

Formula:

=IFERROR(INDEX(SourceDatText,COLUMNS($A:A),ROWS($1:1)),"")

Fill down and across as before.

I believe you will find nflsales dynamic range to be much more flexible. I don't know if the volatile OFFSET will be an issue, though.
You have three formulas to choose from.

I've attached my file. Ctrl + F3 will bring up Name Manager. You can see the range formulas there.

PS...the "frowns" in the formulas above were not intentional. They seem to have replaced some of the text in the formulas.

Originally Posted by Azalea11

maybe the only way is to do it manually -_- Anyways, thank you for all the help.

You are welcome.
If you intend to do this manually, then why don't you make an addin or use a VBScript instead ? This way, you keep your Excel file free from macro(s) and you can save the file in .xlsx format.

This is an example using VBScript, open notepad, paste the code below, and save with ".vbs" file extension and then run it :

TheFile = "Z:\Test.xls"

Set ExcelObject = CreateObject("Excel.Application")

ExcelObject.Workbooks.Open TheFile

Set rng = ExcelObject.Sheets(1).UsedRange

ExcelObject.Sheets(2).Range("A1").Resize(rng.Columns.Count, rng.Rows.Count).value = ExcelObject.WorksheetFunction.Transpose(rng)

ExcelObject.Activeworkbook.save

ExcelObject.Quit

and then you will be known as the fastest typer in the world.

Regards

Auto creation values/formulas without VBA ? Impossible.

You however can enlarge the range to accommodate you future input data, so :

For my formula, select enlarged area, say A1:IV256 and the array formula is :

Formula:

=TRANSPOSE(Sheet1!A1:IV256)

For FlameRetired's formula, it will be :

Formula:

=INDEX(Sheet1!$A$1:$IV$256,COLUMNS($A:A),ROWS($1:1))

But since all the cell's is filled with formula, you will see a performance issue here.
I think your previous way using VBA (Worksheet_Change() event) is the best way for dynamic, irregular data.

Regards

=IFERROR(INDEX(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)),COLUMN(),ROW()),"")
Try this in Sheet 2 and copy across

See Post No 7