copying formula for horizontal data to vertical in different sheet

[twototango]

I have raw data that is horizontal in a worksheet. I need to pull it into a
report, and it needs to be vertical. For example, data is in A1, B1, C1, D1,
E1 ..... In my report sheet I enter ='Raw Data'!A1. I then try to copy my
formula vertically but it copies vertically from my data sheet. When I make
the row absolute by adding $ it still copies vertically even though I leave
the column as relative. I have a lot of data and it takes too long to do one
at a time. Any help would be appreciated. Thanks!

 

[Marcelo]

have you tried to copy and paste special transpose?

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"twototango" escreveu:

 

[twototango]

Hi,

Yes, I tried that but it doesn't work.

have you tried to copy and paste special transpose?

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"twototango" escreveu:

 

[T. Valko]

Try this:

Assume you're wanting the data to start in cell A1:

=INDEX('Raw Data'!A$1:E$1,ROWS(A$1:A1))

Copy down as needed.

 

[twototango]

Biff:

That is soooo excellent!! You have saved me countless hours. You are truly
an MVP!!! I will love you forever!!!!!!!!!!!!!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Jonathan]

This is great but i can't get it to work the other way - vertical to
horizontal - Help!!

My Data runs from D7 to D230 vertically on one sheet and i want it to
display in another sheet from B9 to TT9(or there abouts!)

Regards

 

[Gord Dibben]

Enter this in B9 on destination sheet.

=INDIRECT("Sheet1!D"&COLUMN(G1))

Copy across to TT9 or thereabouts.


Gord Dibben MS Excel MVP

 

[Anita.S.Chau]

whats does the G1 represent in your formula?

i'm trying to use this formula and my data is is array e7:e62 on sheet
1 and i need to put in a formula on a sheet 2 that starts at e20 that
will give me cell e7. when i go to cell f20 on sheet2 it will grab
cell e8 from sheet 1 and so on. please help! thanks!!

 

[Anita.S.Chau]

Hi, please help

My data is on sheet 1 E7:E62. i need this to display horizontally on
sheet 2 starting from E20:BH20. so e20 returns value e7 and f20
returns value e8. please help, thanks!!

 

[T. Valko]

Enter this formula on sheet2 E20 and copy across as needed:

=INDEX(Sheet1!$E7:$E20,COLUMNS($E20:E20))

--
Biff
Microsoft Excel MVP


whats does the G1 represent in your formula?

i'm trying to use this formula and my data is is array e7:e62 on sheet
1 and i need to put in a formula on a sheet 2 that starts at e20 that
will give me cell e7. when i go to cell f20 on sheet2 it will grab
cell e8 from sheet 1 and so on. please help! thanks!!

 

[Gord Dibben]

=INDIRECT("Sheet1!E"&COLUMN(G1))

Enter in E20 and drag across to BH20

The G1 represent the number 7(column G) and increments to 8, 9, 10 as you
drag across.

So....................=INDIRECT("Sheet1!E"&COLUMN(G1)) is equal to
Sheet1!E7

Which changes to Sheet1!E8, Sheet1!E9 etc. as you drag across


Gord