How do you Copy/Link vertical data into a horizontal range? 3

[wec43wec]

My issue is as follows:

1. Data base in range "A1 thru J10" (10 cells across / 10 cells down)

2. Formulas in cell "A12 thru J22"
Note: Data within this range is calculating data from data in range A1 thru J10

Question:

I need to copy/link data from a12 thru A22 (vertical) into cells A25 thru "J"25 (Horizontal), which is similar to how you would do a paste special/transpose.

Is there an easy way to perform this task in Excel 2003?

 

[fredericofonseca]

Why wont the special/transpose work on this situation? Maybe I'm missing your issue.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[wec43wec]

This would work if you manually perform this task as a paste special/"values"/transpose, however I would like for this to be an automatic transfer of values from the cells of A12 thru A22 into A25 thru "J"25.

 

[Gavona]

You could construct a formula =Offset(....) making use of Row and Column functions.
Or you could construct formulae in, say Z12 to Z22 (they would copy down as no transposition. Then cut them from there (individually) to where you want them.

Gavin

 

[bluedragon2]

Well, if putting =A12 in A25, =A13 in B25 and so on doesn't do what you want, you will need a VBA solution. VBA solutions can be posted in forum707

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[wec43wec]

Bluedragon - your solution is exactly what I want to do, however I do not want to manually enter the "=" command into each destination cell.

I want to transfer this vertical data (A12 thru A22) automatically into a horizental range A25 thru J25).

I thought Excel would have a function that would allow me to copy the vertical data and link it to a horizental range.

I will check the "offset" function as mention by Gavon via Excel help to see if this is what I need. Maybe you will have another suggestion

 

[bluedragon2]

Record a macro of you copying the cells then just run the macro.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

This formula should do what you want. You can just drag-and-fill for the entire formula range:
[tab][COLOR=blue white]=Indirect(Address(Column(), Row() - 11))[/color]

But FYI, A12 through J22 is 11 rows. So your formula range will be A12 through J2[red]1[/red].

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GlennUK]

If you want to transpose an area, select the target area, and do =TRANSPOSE(source_area) and press Ctrl-Shift-Enter.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[wec43wec]

You guys -Bluedragon2, Anotherhiggiens and GlenUK are GREAT !


Bluedragon - I like your idea, seems simple amd easy to do. The concept looks simple - thanks !

Anotherhiggins - I can not figure out how to use your solution. I am not sure if I should (in the column location of the formula) enter a cell location for the column or the # of the column within the group of data. I tried both ways but was under to get a result.

GlenUK - OUTSTANDING !!!!!!!!!!!!!!!!!!!!!!!!!!!

I really like your solution, however I do not understand how I got the correct answer. Your solution worked great. Could you or someone else explain to me how the array works?

With all that is said above, each of you must get a STAR, Maybe 2 each for the great solutions.

Thanks

 

[AnotherHiggins]

Glenn is the king of the array formula. Here I thought I was being slick, and then he comes along....

As for my suggestion:

If your data is in A1:J10, then place the formula
[tab]=Indirect(Address(Column(), Row() - 11))
in each of the cells A12:J21.

While you're typing the formula, you'll likely notice that the arguments for the ADDRESS function are as follows:
[tab]Address(Row, Column)
[tab](We aren't using the optional arguments)
I am using the column of the cell which contains the formula to determine which row to return, and vice versa. The "-11" is included because you are starting your formulas on row 12. 12-11 = 1, so it returns data from the first column.

To "drag-and-fill," you'll hover your cursor over the bottom-right hand corner of the cell until the cursor changes to a thick black plus sign (+). Then left click and drag either down or right.

Alternately, you could highlight the entire range A12:J21, Type (or paste) the formula into a single cell and enter using [Ctrl]+[Enter]. Note: don't confuse this with an array formula (entered using [Ctrl]+[Shift]+[Enter]), using [Ctrl]+[Enter] just enters the current entry into all selected cells. And since the same formula is used in all destination cells, that works.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GlennUK]

Hi John,

thanks for the nod. I started using array formulas a long long time ago, and they are now second nature.

and hi wec43wec,

I really like your solution, however I do not understand how I got the correct answer. Your solution worked great. Could you or someone else explain to me how the array works?

... it's quite simple really, as the TRANSPOSE function does all the work. Here's the Excel help description for TRANSPOSE:

Returns a vertical range of cells as a horizontal range, or vice versa. TRANSPOSE must be entered as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) in a range that has the same number of rows and columns, respectively, as an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array on a worksheet.

In fact, the help description just about covers it.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[wec43wec]

GlenUK - thanks, thanks and thanks !

Last question to GlenUK.
Can you point me to a website or reference material that will better allow me to understand the multiple functions of an array.

I see how thge data is used in my example and I have read your/excel's description, however I would like to see and review oher examples of how an array can be used.

Again, thanks and another STAR for you !

 

[GlennUK]

Hi wec43wec,

Chip Pearson has some stuff on arrays here:

http://www.cpearson.com/excel/ArrayFormulas.aspx

and Ozgrid:

http://www.ozgrid.com/Excel/arrays.htm

and from the most famous array expert, Bob Umlas:

http://www.emailoffice.com/excel/arrays-bobumlas.html

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.