auto filling relative worksheet references

  • Thread starter Thread starter John Constable
  • Start date Start date
J

John Constable

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

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