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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

then fill down 5 rows and across 3 columns.

Is this what you meant?

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:

Please Login or Register  to view this content.

For FlameRetired's formula, it will be :

Formula:

Please Login or Register  to view this content.

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

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:

Please Login or Register  to view this content.

In Sheet2 A1 this for numeric:

Formula:

Please Login or Register  to view this content.

This one for text:

Formula:

Please Login or Register  to view this content.

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.

Here's another dynamic formula. It is not so limited as my previous, and is not volatile in case that should become an issue:

=IFERROR(INDEX((Sheet1!$A$1:INDEX(Sheet1!$1:$1,0,COUNTA(Sheet1!$1:$1)))Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))),COLUMNS($A:A),ROWS($1:1)),"")


Hope that's better.

Edit: The frowns keep showing up. Replace that with a colon and an open parentheses.

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 :

Please Login or Register  to view this content.

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

Regards

Originally Posted by FlameRetired

The frowns keep showing up. Replace that with a colon and an open parentheses.

And that is why the CODE tags is very handy

Originally Posted by FlameRetired

Yes, I thought so, too. That's why I didn't use them this time. The frowns showed up with the code tags, too (see post 10). Thought going without

them might help.....I was wrong.

No, I mean code tag (the one with # symbol), not the formula tag (the one with fx symbol).

This is using formula tag :

Formula:

Please Login or Register  to view this content.

This is using code tag :

Please Login or Register  to view this content.

You can quote this post to see the bbcode.
Just sharing an info

Regards

Originally Posted by Azalea11

Thank you for all your help karedog, FlameRetired and nflsales :D I appreciate it so much

You are welcome, glad I can help.

Originally Posted by FlameRetired

Thank you karedog!!

You are welcome, just sharing my non Excel knowledge (the bbcode exactly), hope that this info can help to avoid the future same formatting problems.


Regards