Transpose between sheets

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I need to copy values from sheet2 to sheet1. The values on sheet2 are
in column C cells C5 to C9, however on sheet1 want to place them in
row 7 columns G7, H7,I7,J7 and K7.

In G7 I can place the formula =‘sheet2’!C5, I then want to “drag” the
formula across the row to populate the other cells—so the next cell in
the row should be =‘sheet2’!C6 then =‘sheet2’!C7 etc but every time it
just repeats =‘sheet2’!C5. (I realise I am trying to transpose rows to
columns.)

How can I get it to increase the row number by 1 but keep the column
as C
 
I need to copy values from sheet2 to sheet1. The values on sheet2 are
in column C cells C5 to C9, however on sheet1 want to place them in
row 7 columns G7, H7,I7,J7 and K7.
Click to expand...

One way is to put
=OFFSET(Sheet2!$C$5,COLUMN()-7,0)
in G7 and drag across to K7.
 
Put this in G7 of Sheet1:

=INDEX(Sheet2!$C$5:$C$9,COLUMN(A1))

and then copy across to K7.

COLUMN(A1) returns 1 in the first cell, but then becomes COLUMN(B1) in
the next cell (returning 2) and COLUMN(C1) in the third cell
(returning 3) and so on as it is copied across, so in turn the formula
returns the first cell, second cell etc of the range.

You could use INDIRECT or OFFSET, but as these are volatile functions
it is better to use INDEX.

Hope this helps.

Pete
 
You should be able to do this with a looping macro. First record a
macro while doing one manually to see what is happening>clean it
up>incorporate into the loop.

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 
Different way to do it:

1. On Sheet 1, in a range you're not using for anything else, enter
the formula ='sheet2'!c5 then copy and paste downward to accommodate
through c9.
2. Highlight those formulas, and do a search and replace: replace the
= sign with the letters xyz. The dynamic formula is now changed to a
static text string that looks like this: xyz'sheet2'!c5
3. Copy that range, and move the cursor to your desired location;
right click and select Paste Special. Tick the Transpose box and click
OK.
4. Perform the reverse of step 2: highlight the now horizontal range,
then do a search and replace: search for xyz and replace with the =
sign. Delete the formulas you created in step 1.
5. Take a $20 bill out of petty cash, and have a nice lunch
(optional).

To my mind this is much more straightforward then offsets and
indirects, and much easier to explain and reproduce.

Enjoy!

Dave O
Eschew obfuscation
 
Back
Top