auto filling relative worksheet references
 

I think I might be missing something simple, here...

I have 13 worksheets labeled jan-03 to dec-03 and 'totals for 03'.

On the totals sheet I have a list of months in the first column and next to
each month I want to carry over a total for each month's sheet. (I'm
simplifying this a little as I have a number of totals on each month's sheet
to carry over).

It's getting very tedious typing in every 3d cell reference and I wanted to
use autofill. Can autofill update a worksheet reference from cell to cell as
it would a cell reference on the same sheet e.g. I start on a cell
containing '=jan-03!$A$1' and as I grab the handle and pull down over 11
additional cells each fills with '=feb-03!A$!$' etc?

auto filling relative worksheet references
 

I should also add that I'm unfamiliar with macros and visual basic so if
this is the only answer I'll need my hand holding!

"John Constable" wrote in message
...
I think I might be missing something simple, here...

I have 13 worksheets labeled jan-03 to dec-03 and 'totals for 03'.

On the totals sheet I have a list of months in the first column and next
to
each month I want to carry over a total for each month's sheet. (I'm
simplifying this a little as I have a number of totals on each month's
sheet
to carry over).

It's getting very tedious typing in every 3d cell reference and I wanted
to
use autofill. Can autofill update a worksheet reference from cell to cell
as
it would a cell reference on the same sheet e.g. I start on a cell
containing '=jan-03!$A$1' and as I grab the handle and pull down over 11
additional cells each fills with '=feb-03!A$!$' etc?

auto filling relative worksheet references
 

John,

Here is an example that assumes that A2:A13 on 'totals-03' holds the values
jan, feb, etc, and picks up the values from A1 on the monthly sheets

=INDIRECT("'"&A2&"-03'!A1")

just copy down, and build to suit

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Constable" wrote in message
...
I should also add that I'm unfamiliar with macros and visual basic so if
this is the only answer I'll need my hand holding!

"John Constable" wrote in message
...
I think I might be missing something simple, here...

I have 13 worksheets labeled jan-03 to dec-03 and 'totals for 03'.

On the totals sheet I have a list of months in the first column and next
to
each month I want to carry over a total for each month's sheet. (I'm
simplifying this a little as I have a number of totals on each month's
sheet
to carry over).

It's getting very tedious typing in every 3d cell reference and I wanted
to
use autofill. Can autofill update a worksheet reference from cell to
cell
as
it would a cell reference on the same sheet e.g. I start on a cell
containing '=jan-03!$A$1' and as I grab the handle and pull down over
11
additional cells each fills with '=feb-03!A$!$' etc?

auto filling relative worksheet references
 

use the indirect function and build the reference with a formula

=indirect(format(Date(2003,row(A1),1)."mmm-yy" & "!A1"))

row(A1) is used to generate an integer (1 to 12) to indicate the month.

--
Regards,
Tom Ogilvy



"John Constable" wrote in message
...
I think I might be missing something simple, here...

I have 13 worksheets labeled jan-03 to dec-03 and 'totals for 03'.

On the totals sheet I have a list of months in the first column and next
to
each month I want to carry over a total for each month's sheet. (I'm
simplifying this a little as I have a number of totals on each month's
sheet
to carry over).

It's getting very tedious typing in every 3d cell reference and I wanted
to
use autofill. Can autofill update a worksheet reference from cell to cell
as
it would a cell reference on the same sheet e.g. I start on a cell
containing '=jan-03!$A$1' and as I grab the handle and pull down over 11
additional cells each fills with '=feb-03!A$!$' etc?

auto filling relative worksheet references
 

Thanks, everyone - a working solution is now in place.

"Tom Ogilvy" wrote in message
...
use the indirect function and build the reference with a formula

=indirect(format(Date(2003,row(A1),1)."mmm-yy" & "!A1"))

row(A1) is used to generate an integer (1 to 12) to indicate the month.

--
Regards,
Tom Ogilvy



"John Constable" wrote in message
...
I think I might be missing something simple, here...

I have 13 worksheets labeled jan-03 to dec-03 and 'totals for 03'.

On the totals sheet I have a list of months in the first column and next
to
each month I want to carry over a total for each month's sheet. (I'm
simplifying this a little as I have a number of totals on each month's
sheet
to carry over).

It's getting very tedious typing in every 3d cell reference and I wanted
to
use autofill. Can autofill update a worksheet reference from cell to
cell
as
it would a cell reference on the same sheet e.g. I start on a cell
containing '=jan-03!$A$1' and as I grab the handle and pull down over
11
additional cells each fills with '=feb-03!A$!$' etc?