Microsoft Microsoft excel 2004 (mac)

Hi. I have data on Sheet 1 which I need to transpose to Sheet 2...

Current data:

1 1 1

2 2 2

3 3 3 etc.Required result:

1

1

1

2

2

2 etc.I am using the following array function in cells A1:A3 on Sheet 2:

{=transpose('Sheet1'A1:C1)}This works fine, except that when I copy it to the next three cells, the formula jumps by three rows instead of one, thus the results on Sheet 2:

A1:A3 = 1, 1, 1 (correct)

A4:A6 = 4, 4, 4 (wrong - should be 2, 2, 2)How can I adjust the formula to tell Excel to look at the second row instead of the fourth on Sheet 1? I am totally stumped!

If your data is really arranged as you say, then this formula will get you the results you want. If you have more than 3 columns, you'll need to make adjustments, but the concept works:

=INDEX(Sheet1!$A$1:$C$3,COLUMN()+INT(ROW()/3.1),ROW()-INT(ROW()/3.1)*3)

How it works:

The INDEX function pulls values from an Array (Sheet1!$A$1:$C$3) by using a

row_numand acol_num.For the

row_numI'm using COLUMN()+INT(ROW()/3.1).Since the formula is in Column 1, this will evaluate to:

1 + 0 for rows 1, 2 and 3

1 + 1 for Rows 4, 5 and 6

1 + 2 for Rows 7, 8 and 9

Therefore your

row_numvalues for the INDEX function will 1, 1, 1, 2, 2, 2, 3, 3, 3 for A1:A6The same concept is used for to calculate the

col_numvalues for the INDEX function, except that I use the ROW number for the calculations.If you have more than 3 columns, just change the 3.1 to be 4.1 or 5.1 or whatever.

Have fun!

Hi DerbyDad03, and thanks so much for your reply. Sadly, I don't think I can get it to work. You see, the numbers I gave were just for the purpose of giving a simple, clear example. But the actual data I want to transpose is from a list of photo URLs, and so is text and not numbers. Here's the actual layout of the data: Sheet 1

Column A Column B Column C

http://url/0001a.jpg http://url/0001b.jpg http://url/0001c.jpg

http://url/0002a.jpg http://url/0002b.jpg http://url/0002c.jpgAnd here's what I need it to look like on the second sheet:

Sheet 2

Column A

http://url/0001a.jpg

http://url/0001b.jpg

http://url/0001c.jpg

http://url/0002a.jpg

http://url/0002b.jpg

http://url/0002c.jpgI'm guessing this makes things a whole lot more difficult...? :)

Hi, DerbyDad03's formula works fine.

It doesn't matter what is in the cells in Sheet1.

Regards

Have you tried my suggested solution? The formula displays the contents of the cells in a single column (assuming you start the formula in A1) regardless of what is in the orginal cells. It could be a hard-coded value, the result of a formula or, yes, even a URL.

I just tested it on your new example data and it works fine.

If the problem is that you are not getting actual links after using the formula, try this:

- Right click the sheet tab for the sheet with new list.

- Paste the following code in the pane that opens.

- Change to "9" to however many rows you have in the new list

- Run the code to (hopefully) recreate the links.

Disclaimer: I suggest you make a backup copy of your workbook in case something goes terribly wrong.Option Explicit Sub Recreate_Links() Dim myRow, myLink For myRow = 1 To 9 myLink = Range("A" & myRow).Text ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & myRow), Address:= _ myLink, TextToDisplay:=myLink Next End Sub

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History