Transpose cell contents from multiple worksheets into rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
There is a TRANSPOSE function in excel to transpose row entries to column
entries and vice versa. Is this what you're looking for?
 
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).
 
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.
 
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
 
Gord Dibben said:
You are not looking to "transpose, but looking to "transfer" A1 from each
worksheet to one column in a summary sheet.
Click to expand...

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
Click to expand...
....

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.
 
Back
Top