copying formula for horizontal data to vertical in different sheet

  • Thread starter Thread starter twototango
  • Start date Start date
T

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!
 
have you tried to copy and paste special transpose?

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



"twototango" escreveu:
 
Hi,

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

Marcelo said:
have you tried to copy and paste special transpose?

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



"twototango" escreveu:
Click to expand...
 
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.
 
Biff:

That is soooo excellent!! You have saved me countless hours. You are truly
an MVP!!! I will love you forever!!!!!!!!!!!!!
 
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
 
Enter this in B9 on destination sheet.

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

Copy across to TT9 or thereabouts.


Gord Dibben MS 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!!
 
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!!
 
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!!
 
=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
 
Back
Top