software

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???

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

One way ..

We'll create a defined range quot;Monthsquot; first
Click Insert gt; Name gt; Define
Under Names in workbook, input: Months
Under Refers to, paste:
={quot;Janquot;;quot;Febquot;;quot;Marquot;;quot;Aprquot;;quot;Mayquot;;quot;Junquot;;quot;Julquot;;quot;Augquot;; quot;Sepquot;;quot;Octquot;;quot;Novquot;;quot;Decquot;}
Click OK

Then in sheet: Total,
Put in A1: =INDIRECT(INDEX(Months,ROW(A1))amp;quot;!A1quot;)
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))amp;quot;!A1quot;)
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))amp;quot;!A1quot;),,COL UMN(A1)-1)
then copy A1 across as desired to say, AZ1,
and fill down to AZ12
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---