Auto fill for data from another worksheet

[Guest]

I am trying to setup a budget book in Excel 2000. There is a schedule sheet
to set budgeted values, twelve sheets for months, and one sheet for a yearly
summary.

My problem is as follows:

Each monthly sheet has totals for different budget categories which are to
be transferred to the yearly summary sheet.

The cell values to be transferred are of the nature -
January!AE7, January!AE8 ..... January!AE32
February!AE7, February!AE8......February!AE32
. . .
. . .
December!AE7, December!AE8.....December!AE32

In the yearly summary if I try to auto fill a row by typing in two cell
values such as January!AE 7, January!AE8, highlighting the two cells, and
dragging I get-
January!AG7, January!AH7, etc which is wrong.

If I try to auto fill a colum by typing in two cell values such as
January!AE7
February!AE7
highlighting the two cells, and dragging I get-

January!AE9
February!AE9, etc which is also wrong.

I tried using Edit, Fill, Series and also Tools, Options, Custom Series to
set up a proper autofill series but nothing works. Is there a way to do what
I am attempting?

 

[Dave Peterson]

I think that this'll work:

Put this in the top left cell (the =January!AE7 cell):

="$$$$=" &TEXT(DATE(2006,ROW(A1),1),"mmmm")&"!AE"&COLUMN(A1)+6

Then copy it across as far as you need
select those cells and drag down 11 rows.

You'll end up with cells that look like:

$$$$=January!AE7 $$$$=January!AE8 $$$$=January!AE9 $$$$=January!AE10
$$$$=February!AE7 $$$$=February!AE8 $$$$=February!AE9 $$$$=February!AE10
$$$$=March!AE7 $$$$=March!AE8 $$$$=March!AE9 $$$$=March!AE10
$$$$=April!AE7 $$$$=April!AE8 $$$$=April!AE9 $$$$=April!AE10
$$$$=May!AE7 $$$$=May!AE8 $$$$=May!AE9 $$$$=May!AE10

Verify that the formulas look ok and that each sheet name actually exists--else
you'll be dismissing a bunch of "where's this file" dialogs!


Now select that whole range
edit|copy
edit|Paste special|values

And then change that $$$$ to nothing
select the range
edit|Replace
what: $$$$
with: (leave blank)
replace all

You should end up with formulas.

 

[Peo Sjoblom]

=January!AE7

copied down will return,

=January!AE8

If you want to "increment" the month while keeping the cell address?

then the easiest way would be to create a regular list with the months
(maybe off the screen somewhere if you want it to look more fancy) Assume
the list with January to December are in H1:H12, then this formula will
return JanuaryAE7, FebruaryAE7 and so on

=INDIRECT("'"&H1&"'!AE7")

without a help column

=INDIRECT("'"&INDEX({"January","February","March","April","May","June","July","August","September","October","November","December"},MIN(12,ROWS($A$1:A1)))&"'!AE7")

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Guest]

Two very nice solutions. Thanks very much.