Help Format problem Columns to Rows

[Dab]

I'm hoping that someone can help with this problem.

I want to populate some columns with the data from some rows. A kind of
'transpose' function

The numbers of rows under a particular heading will change as I cut and
paste rows under different headings. I'd like to see the same row
information in columns elsewhere in the page.

The trouble is that as I cut and past the rows, the relative reference to
the data in those rows tracks and the target columns show the same
information regardless of where the row has been pasted. (This probably
isn't making much sense).

I could make an absolute reference, but the problem is that the number of
rows will change so using an absolute reference will not transfer the proper
data to the columns. One way I could do it (not very elegant) is to use an
absolute reference to a fixed number of rows. That way, as I moved rows
around the reference won't change and the columns will display properly.
The problem is that I have to use enough blank rows to cover all possible
cut and past combinations. This could lead to quite a large sheet with a
lot of blank rows.

Any other suggestions? Thanks for any advice.

(Sorry if this is all a bit confusing)

 

[Dab]

Well I found a solution to my problem, but it's really gross......

=IF(INDEX($A$57:$C$156,MATCH($G13,$A$57:$A$165,)-2,2)-(COLUMN(H11)-7)<0,"",(INDEX($A$57:$C$156,MATCH($G12,$A$57:$A$165,)+COLUMN(H11)-7,3)))

The first part simply determines if a cell should be blank by checking the
total number of entries elsewhere in the sheet.

IF(INDEX($A$57:$C$156,MATCH($G13,$A$57:$A$165,)-2,2)-(COLUMN(H11)-7)<0,""

The second part looks up the cell value in a range by calculating an index
offset using a match function. The Column function is added so that when
the formula is copied, to the next column, the index increases by 1.

INDEX($A$57:$C$156,MATCH($G12,$A$57:$A$165,)+COLUMN(H11)-7,3))

It works, but I was thinking that there could be a simpler solution