Transpose cell contents from multiple worksheets into rows
 

I believe that in Lotus or perhaps an older version of Excel, I was able to
choose 2 of the 3 dimensions to transpose from/to: row, columns, and
worksheets. Can Excel 2003 do this?

Transpose cell contents from multiple worksheets into rows
 

There is a TRANSPOSE function in excel to transpose row entries to column
entries and vice versa. Is this what you're looking for?

"v-words" wrote in message
...
I believe that in Lotus or perhaps an older version of Excel, I was able to
choose 2 of the 3 dimensions to transpose from/to: row, columns, and
worksheets. Can Excel 2003 do this?

Transpose cell contents from multiple worksheets into rows
 

I am specifically trying to transpose a cell, for example cell A1, from
multiple worksheets into rows (i.e., down one column in one worksheet).

Transpose cell contents from multiple worksheets into rows
 

How many sheets are you talking about? If only a few, you can do it manually
with ='sheetname'!A1 in each row.
where sheetname is the name of the individual sheet.

"v-words" wrote in message
...
I am specifically trying to transpose a cell, for example cell A1, from
multiple worksheets into rows (i.e., down one column in one worksheet).

Transpose cell contents from multiple worksheets into rows
 

There are 75+ worksheets. The tabs all have long names rather than Sheet1,
Sheet2, etc.

Transpose cell contents from multiple worksheets into rows
 

You are not looking to "transpose, but looking to "transfer" A1 from each
worksheet to one column in a summary sheet.

One way would be to use a macro to get all sheet names into a list on summary
sheet

Private Sub ListSheets()
'list of sheet names starting at A1 down Column A
Dim Rng As Range
Dim i As Integer
Set SummarySheet = Worksheets.Add
SummarySheet.Name = "Summary"
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name < "Summary" Then
Rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
End If
Next Sheet
End Sub

Now you can enter in B1 =INDIRECT("'" & A1 & "'!A1")

Double-click on fill handle of B1 to copy down.


Gord Dibben MS Excel MVP

On Tue, 17 Jul 2007 13:02:09 -0700, v-words
wrote:

There are 75+ worksheets. The tabs all have long names rather than Sheet1,
Sheet2, etc.

Transpose cell contents from multiple worksheets into rows
 

"Gord Dibben" <gorddibbATshawDOTca wrote...
You are not looking to "transpose, but looking to "transfer" A1 from each
worksheet to one column in a summary sheet.

Actually, transposition generalizes as an permutation of coordinates, so it
may be applied to more than 2 dimensions. The OP is recalling this feature
from Lotus 123, which provides 3D transposes (as does Quattro Pro, FWLIW).

One way would be to use a macro to get all sheet names into a list on
summary sheet
....

Or the OP could avoid VBA.

If the other worksheets were grouped together, then the OP could define the
following names referring to the following formulas. Note: for safety in
older versions, this should be done in a new worksheet, which I'll name ' '.

' '!_WSLST:
=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.DOCUMENT(88)&"]","")

WSLST:
=' '!$1:$1

WSOFF:
=MATCH("FirstWorksheet",WSLST,0)-1

Enter the array formula =_WSLST in WSLST, i.e., in ' '!$1:$1. The string
constant "FirstWorksheet" is a placeholder for the name of the first of
these worksheets.

Then you could pull the values of the X99 cells from each of the worksheets
using formulas like the following in cell Y33.

Y33:
=INDIRECT("'"&INDEX(WSLST,ROWS(Y$33:Y33)+WSOFF)&"' !X99")

Fill Y33 down as far as needed.