Transform matrix format sheet into flat file format sheet with lin

[Felix]

I need to consolidate and analyse a set of spreadsheets with identical
structure ( 1 set of 26 sheets with 378 data cells, 1 set of 26 sheets with
954 data cells)
1. For the totalling, I use the Consolidation function of Excel. However,
this requires you to have all sheets/ ranges selected before you can execute
the consolidate, because if you miss one, e.g. because it is not yet in, you
have to redo the consolidation, i.e. specify all sheets and ranges again.
Is there a feature that lest you do 'running consolidations', i.e. add
sheets / ranges as the data come in, and do intermediate consolidations?
2. i also need to perform a no. of analyses on the combination of the detail
data from all sheets, for which I use Pivot tables.
However, this requires you to convert the data from each sheet from a matrix
format to flat file format (otherwise Pivot tables will not work). This
conversion is a tedious and error prone work, as you cannot do a paste link
with simultaneous transpose of a row of cells into a column of cells.
Is there a way to do such combined paste link and transpose of a range of
cells?
Think, then move

 

[JE McGimpsey]

I need to consolidate and analyse a set of spreadsheets with identical
structure ( 1 set of 26 sheets with 378 data cells, 1 set of 26 sheets with
954 data cells)
1. For the totalling, I use the Consolidation function of Excel. However,
this requires you to have all sheets/ ranges selected before you can execute
the consolidate, because if you miss one, e.g. because it is not yet in, you
have to redo the consolidation, i.e. specify all sheets and ranges again.
Is there a feature that lest you do 'running consolidations', i.e. add
sheets / ranges as the data come in, and do intermediate consolidations?

One way:

Create a blank sheet (named, say, "Start1") to the left of the first
sheet with 378 cells, and a blank sheet to the right of the last sheet
with 378 cells (named, say, "End1"). Then, in your summary sheet, enter

A1: =SUM(Start1:End1!A1)

Copy down and over as needed. You can hide the sheets if you want. Do
the same thing with the other set.

2. i also need to perform a no. of analyses on the combination of the detail
data from all sheets, for which I use Pivot tables.
However, this requires you to convert the data from each sheet from a matrix
format to flat file format (otherwise Pivot tables will not work). This
conversion is a tedious and error prone work, as you cannot do a paste link
with simultaneous transpose of a row of cells into a column of cells.
Is there a way to do such combined paste link and transpose of a range of
cells?

One can use the INDEX function to transpose. For instance to transpose
Sheet2!B5:F50 to Sheet1!A1:AT5, put this in Sheet1:

A1: =INDEX(Sheet2!$A:$F,COLUMN()+4,ROW()+1)

Adjust thee row and column offsets to suit.

 

[Felix]

In its basic form, the INDEX formula works; however, it displays some in my
eyes strange behaviour:
if I soecify as target range where to put the cells an array that does not
start in cell a1, then it no longer displays the correct source range, but a
range with an off-set to that, e.g.
- source range = sheet2 E5:AD5, containing the numbers 1-26
- target range =
A. sheet 1 A1:A26, formula =INDEX(Costs!E:AD,COLUMN()+4, ROW()+0), then the
numbers 1-26 are displayed correctly
B. however, if I use the exact same formula, but put it in the target range
B1:B26, (or C1:C26 or other), then it does not display the correct range.
Aslo, if you copy the formula in the target range to a different place, it no
longer displays the correct source range (even after correcting the automatic
formula shift generated by the copying)
I have verified my Tools - Options settings, but do not see any settig that
might cause this strange behaviour.
Any explanation for this?

 

[Bob Greenblatt]

In its basic form, the INDEX formula works; however, it displays some in my
eyes strange behaviour:
if I soecify as target range where to put the cells an array that does not
start in cell a1, then it no longer displays the correct source range, but a
range with an off-set to that, e.g.
- source range = sheet2 E5:AD5, containing the numbers 1-26
- target range =
A. sheet 1 A1:A26, formula =INDEX(Costs!E:AD,COLUMN()+4, ROW()+0), then the
numbers 1-26 are displayed correctly
B. however, if I use the exact same formula, but put it in the target range
B1:B26, (or C1:C26 or other), then it does not display the correct range.
Aslo, if you copy the formula in the target range to a different place, it no
longer displays the correct source range (even after correcting the automatic
formula shift generated by the copying)
I have verified my Tools - Options settings, but do not see any settig that
might cause this strange behaviour.
Any explanation for this?

This is not strange behavior, but as designed. No settings will correct
this. I think you need to review the difference between absolute and
relative references.

When you copy and paste this formula to another cell, the input range
reference will be offset based on the location of the cell you are pasting
into relative to the source cell9s). I think you probably want your formula
to read costs!$E$5:$AD$5.

 

[Felix]

Bob,

It's not that; I know the difference between absolute and relative
referencing; it is only with the INDEX function that I experience this
strange behaviour, with no other one.

 

[Bob Greenblatt]

Bob,

It's not that; I know the difference between absolute and relative
referencing; it is only with the INDEX function that I experience this
strange behaviour, with no other one.

OK, Can you describe again what behavior you are seeing? Does your index
function use an absolute reference to the array?