Columna to Rows Formula

[Mike]

I have entered transactions in columns in Sheet1 and need to set up formulas
in Sheet2 to bring that data into Rows in order to set up a table for
filtering and sorting.
Example: Sheet1 the transactions are entered as folllows:
C5:C59, D559, E5:E59 and so on...

I want those transactions to go into Sheet2, beggining at A6, in rows as
follows:
A6:BC6 would be Sheet1 C5:C59
A7:BC7 would be Sheet1 D559
A8:BC8 would be Sheet1 E5:E59
and so on...

Special formulas???

Your help is appreciated!!!

 

[Bob Phillips]

For A6

=INDEX(Sheet1!$C$5:$C$59,COLUMN())

and copy across,

etc.

 

[Mike]

Bob,

I entered the formula and the cell returns #NAME?

Mike

 

[Mike]

Bob,
Actually the formula returns the first transaction in sheet1 column C in
sheet2 Row 6 down to row 250 in the table???

Mike

 

[Sheeloo]

Tr
=INDEX(INDIRECT("Sheet1!$"&CHAR(61+ROW())&"$5:$"&CHAR(61+ROW())&"$59"),COLUMN())

in A6 (on Sheet2) and copy down and across

 

[Mike]

Works perfectly down to row 30 then 30-35 is #REF!
then row 36 is 00000 whiich is ok
then row 37 is colum headings
then row38-60 repeats data from sheet1
then row 62-194 is #REF!
then row 195-250 is #VALUE!

I have currently input transactions into sheet1 Column C,D,F but will enter
a few hundred transactions this week. Thus sheet2 row 6,7,8 should show the
three transaction, which it does, but all other rows should show 0's

 

[Sheeloo]

I did not think beyond column Z..

Try
=INDEX(INDIRECT("Sheet1!R5C"&(ROW()-3)&":R59C"&(ROW()-3),FALSE),COLUMN())

This should work for all...

 

[Mike]

Sheeloo,

Works Perfect. But, I can't filter sort the data in the table. I think it
is because of the INDIRECT in the formula???

Thanks, Mike

 

[Sheeloo]

You are partially right...
Since the formula is based on Row() sorting will not have any impact.
Indirect just passes the string as an address to Index...

You can copy and paste special as values if you want to sort but then
updates in Sheet1 won't carry over.

Filter should work...

 

[Mike]

Thank you very much!!!

I will copy and special paste values & number formats as reports are
required. I was hoping there was a way to sort without doing that.

Thanks, Mike