I have 13 worksheets (Jan. - Dec. and Total). The Total worksheet has 12
columns (Jan. - Dec.) Total!A1 = Jan!$A$1. How can I copy the reference from
Total!A1 to Total!A2, Total!A3 all the way to Total!12 so that I get
Feb!$A$1, Mar!$A$1 thru Dec!$A$1???

"jrw" wrote:
I have 13 worksheets (Jan. - Dec. and Total).
The Total worksheet has 12 columns (Jan. - Dec.)
Total!A1 = Jan!$A$1.
How can I copy the reference from
Total!A1 to Total!A2, Total!A3 all the way to
Total!12 so that I get Feb!$A$1, Mar!$A$1 thru Dec!$A$1?

One way ..

We'll create a defined range "Months" first
Click Insert Name Define
Under Names in workbook, input: Months
Under Refers to, paste:
={"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug"; "Sep";"Oct";"Nov";"Dec"}
Click OK

Then in sheet: Total,
Put in A1: =INDIRECT(INDEX(Months,ROW(A1))&"!A1")
Copy A1 down to A12
A1:A12 will return the contents from: Jan!A1, Feb!A1, etc

And if we wanted it to fill across (instead of down)
just change ROW to COLUMN, i.e. put in A1:
=INDIRECT(INDEX(Months,COLUMN(A1))&"!A1")
and copy A1 to L1
A1:L1 will return the contents from: Jan!A1, Feb!A1, ... Dec!A1

Lastly, a slight twist to it ..
if we wanted it to fill across and down from A1 as:

Jan!A1, Jan!B1, Jan!C1 ..
Feb!A1, Feb!B1, Feb!C1 ..
etc

we could put in A1:
=OFFSET(INDIRECT(INDEX(Months,ROW(A1))&"!A1"),,COL UMN(A1)-1)
then copy A1 across as desired to say, AZ1,
and fill down to AZ12
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---