software

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?

--
Frustrated Accountant (or am I an engineer? - I can''t remember anymore)

I think that this'll work:

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

=quot;$$$$=quot; amp;TEXT(DATE(2006,ROW(A1),1),quot;mmmmquot;)amp;quot;!AEquot;amp;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 quot;where's this filequot; 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.

Frustrated Accountant wrote:
gt;
gt; I am trying to setup a budget book in Excel 2000. There is a schedule sheet
gt; to set budgeted values, twelve sheets for months, and one sheet for a yearly
gt; summary.
gt;
gt; My problem is as follows:
gt;
gt; Each monthly sheet has totals for different budget categories which are to
gt; be transferred to the yearly summary sheet.
gt;
gt; The cell values to be transferred are of the nature -
gt; January!AE7, January!AE8 ..... January!AE32
gt; February!AE7, February!AE8......February!AE32
gt; . . .
gt; . . .
gt; December!AE7, December!AE8.....December!AE32
gt;
gt; In the yearly summary if I try to auto fill a row by typing in two cell
gt; values such as January!AE 7, January!AE8, highlighting the two cells, and
gt; dragging I get-
gt; January!AG7, January!AH7, etc which is wrong.
gt;
gt; If I try to auto fill a colum by typing in two cell values such as
gt; January!AE7
gt; February!AE7
gt; highlighting the two cells, and dragging I get-
gt;
gt; January!AE9
gt; February!AE9, etc which is also wrong.
gt;
gt; I tried using Edit, Fill, Series and also Tools, Options, Custom Series to
gt; set up a proper autofill series but nothing works. Is there a way to do what
gt; I am attempting?
gt;
gt; --
gt; Frustrated Accountant (or am I an engineer? - I can''t remember anymore)

--

Dave Peterson

=January!AE7

copied down will return,

=January!AE8

If you want to quot;incrementquot; 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(quot;'quot;amp;H1amp;quot;'!AE7quot;)

without a help column

=INDIRECT(quot;'quot;amp;INDEX({quot;Januaryquot;,quot;Februaryquot;,quot;Marchquot;, quot;Aprilquot;,quot;Mayquot;,quot;Junequot;,quot;Julyquot;,quot;Augustquot;,quot;Septemberquot;,quot; Octoberquot;,quot;Novemberquot;,quot;Decemberquot;},MIN(12,ROWS($A$1:A 1)))amp;quot;'!AE7quot;)

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

quot;Frustrated Accountantquot; gt;
wrote in message ...
gt;I am trying to setup a budget book in Excel 2000. There is a schedule
gt;sheet
gt; to set budgeted values, twelve sheets for months, and one sheet for a
gt; yearly
gt; summary.
gt;
gt; My problem is as follows:
gt;
gt; Each monthly sheet has totals for different budget categories which are to
gt; be transferred to the yearly summary sheet.
gt;
gt; The cell values to be transferred are of the nature -
gt; January!AE7, January!AE8 ..... January!AE32
gt; February!AE7, February!AE8......February!AE32
gt; . . .
gt; . . .
gt; December!AE7, December!AE8.....December!AE32
gt;
gt; In the yearly summary if I try to auto fill a row by typing in two cell
gt; values such as January!AE 7, January!AE8, highlighting the two cells, and
gt; dragging I get-
gt; January!AG7, January!AH7, etc which is wrong.
gt;
gt; If I try to auto fill a colum by typing in two cell values such as
gt; January!AE7
gt; February!AE7
gt; highlighting the two cells, and dragging I get-
gt;
gt; January!AE9
gt; February!AE9, etc which is also wrong.
gt;
gt; I tried using Edit, Fill, Series and also Tools, Options, Custom Series to
gt; set up a proper autofill series but nothing works. Is there a way to do
gt; what
gt; I am attempting?
gt;
gt; --
gt; Frustrated Accountant (or am I an engineer? - I can''t remember anymore)Two very nice solutions. Thanks very much.
--
Frustrated Accountant (or am I an engineer? - I can''''t remember anymore)quot;Frustrated Accountantquot; wrote:

gt; I am trying to setup a budget book in Excel 2000. There is a schedule sheet
gt; to set budgeted values, twelve sheets for months, and one sheet for a yearly
gt; summary.
gt;
gt; My problem is as follows:
gt;
gt; Each monthly sheet has totals for different budget categories which are to
gt; be transferred to the yearly summary sheet.
gt;
gt; The cell values to be transferred are of the nature -
gt; January!AE7, January!AE8 ..... January!AE32
gt; February!AE7, February!AE8......February!AE32
gt; . . .
gt; . . .
gt; December!AE7, December!AE8.....December!AE32
gt;
gt; In the yearly summary if I try to auto fill a row by typing in two cell
gt; values such as January!AE 7, January!AE8, highlighting the two cells, and
gt; dragging I get-
gt; January!AG7, January!AH7, etc which is wrong.
gt;
gt; If I try to auto fill a colum by typing in two cell values such as
gt; January!AE7
gt; February!AE7
gt; highlighting the two cells, and dragging I get-
gt;
gt; January!AE9
gt; February!AE9, etc which is also wrong.
gt;
gt; I tried using Edit, Fill, Series and also Tools, Options, Custom Series to
gt; set up a proper autofill series but nothing works. Is there a way to do what
gt; I am attempting?
gt;
gt; --
gt; Frustrated Accountant (or am I an engineer? - I can''t remember anymore)