I have a worksheet that has a list of cities as row headers, and dates as
column headers with numeric values for data. It shows the # of new
construction permits issued in each metro area per month.

I need to select all the values for a city which are in a row, and paste
them into a column on the worksheet which will do the calculations I need. I
can't figure out how to change the source worksheet so that the cities are
columns and the dates are rows so I can do a simply copy/paste.

Is there a formula or something I can give to the first cell of the
destination column that will let me identify the first cell of the source row
and then automatically fill the rest of the destination column with the
values in the source row?

Ian,

You can try two things:

(1) You can copy the data in a row and transpose it into a column and visa
versa. Assuming you want to copy a column to a row, select the column of
data. Go to the standard toolbar and select Edit - Copy. Go to the first
cell of the row where you want to paste the data. Go to the standard toolbar
and select Edit - Paste Special - Transpose. Then hit the OK button. The
Transpose option is at the bottom right side of the Paste Special dialog box.

(2) You can use the TRANSPOSE worksheet function (might want to look in
Excel's on-line help for this) in a formula.

----
Regards,
John Mansfield
http://www.pdbook.com

"Ian Johnson" wrote:


I have a worksheet that has a list of cities as row headers, and dates as
column headers with numeric values for data. It shows the # of new
construction permits issued in each metro area per month.

I need to select all the values for a city which are in a row, and paste
them into a column on the worksheet which will do the calculations I need. I
can't figure out how to change the source worksheet so that the cities are
columns and the dates are rows so I can do a simply copy/paste.

Is there a formula or something I can give to the first cell of the
destination column that will let me identify the first cell of the source row
and then automatically fill the rest of the destination column with the
values in the source row?

Another way to try is to use OFFSET() ..

Assuming the sample source data below (a 5R x 4C grid)
is in Sheet1, A1:D5, with the top left cell A1 blank

<A1 Date1 Date2 Date3
City1 19717 19636 11300
City2 17396 15068 17064
City3 17319 12246 18302
City4 14320 12195 11035

In Sheet2
-----------
Put in say, A1:

=OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1)-1,ROWS($A$1:A1)-1)

Copy A1 across to E1, fill down to E4
(or copy down and fill across)
to cover a "converse" grid of 4R x 5C

Format col A as dates

And for a neater look, we could also suppress
extraneous zeros from showing in the sheet via clicking:
Tools Options View tab Uncheck "Zero values" OK

< B* City1 City2 City3 City4
Date1 19717 17396 17319 14320
Date2 19636 15068 12246 12195
Date3 11300 17064 18302 11035

B* = blank top left cell (with zero values suppressed)

Adapt to suit:

Just change the top left cell (A1) and the sheetname ref
of the source table in the formula: OFFSET(Sheet1!$A$1 ...
to say: OFFSET(MyData!$B$2 ...
(if your source data is in sheet: MyData, with top left cell in B2)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Ian Johnson" <Ian wrote in message
...

I have a worksheet that has a list of cities as row headers, and dates as
column headers with numeric values for data. It shows the # of new
construction permits issued in each metro area per month.

I need to select all the values for a city which are in a row, and paste
them into a column on the worksheet which will do the calculations I need.
I
can't figure out how to change the source worksheet so that the cities are
columns and the dates are rows so I can do a simply copy/paste.

Is there a formula or something I can give to the first cell of the
destination column that will let me identify the first cell of the source
row
and then automatically fill the rest of the destination column with the
values in the source row?