Excel - Inserting Lines Makes Formula Inactive

Asked By lktx

28-Jan-10 06:48 PM

I downloaded a Microsoft template called General Ledger (Green,
multi-sheet/one per account code plus YTD Budget Summary and Monthly Expenses
sheets).

The workbook uses the name manager and some other field names that I cannot
find definitions for.

The document worked fabulously until 1/1/10.  Now, when I add a line to any
of the individual account sheets, the linked data result for the formulas on
the Monthly Expenses table disappear.  The formulas are there but the result,
and the "Accounting" format just go away.

No matter what I do, the Monthly Expenses table stops working when I make
any adjustments to data on the account sheets.

Has anyone else used this workbook?  Something must be embedded in it that I
just cannot figure out.

HELP PLEASE!  The invoices are piling up!!

28-Jan-10 07:14 PM

Please post the URL for download.

I can have a look at it.


Gord Dibben  MS Excel MVP

01-Feb-10 10:25 AM

http://office.microsoft.com/en-us/templates/TC100738801033.aspx?CategoryID=CT101441121033

Thank you!  Even if you can tell me how the column names are able to be used
in the formulas without defining the name, that would be helpful.  Then,
worst case scenario, I can recreate the whole thing without whatever embedded
code is making it malfunction.

Thanks again.

L

01-Feb-10 02:41 PM

I think your problem with the Ledger workbook is the use of Tables.

Here are some basics of Excel 2007 and Tables/column names in formulas.
......................................................................................................

In 2007 you first create a Table by selecting a range that includes column
names(titles) in row 1 of your range.

Insert>Table>Create Table.  You can name the Table or accept the default
Table(number)  that Excel provides.

You can change table name at any time by selecting anywhere in the Table and
up at top right click on Table Tools>Design.

You can also Resize the Table while in Design mode.  See my thoughts on this
below.

...................................................................................................

Back to Ledger Template...........

Sheet named  "1000-Office"  has a Table named Office sized to A1:J6
and a column title of Amount of Check.

To sum the data in that column of that Table you enter....outside the table
range........... =SUM(Office[Amount of Check])

Using example above see how it works on Monthly Expenses sheet in C5

=SUMIF(Office[Constructed
KeyField],"="&(TEXT(Expenses[[#Headers],[Jan-07]],"mmm-yy")),Office[Amount
of Check])

I think re-sizing tables will solve your problem.

All sheets in the Ledger workbook have Tables with column names.

Hope this clears it up some little bit.

For more on this see help on Table Names


Gord

02-Feb-10 11:18 AM

I checked every page and the table ranges are correct.  I also recreated the
sheets and tables and used accurate formulas and I cannot get the numbers to
show up on the Monthly page.  Even on the recreated page, I get zeros.

I just do not get it - how could the workbook work perfectly for months and
then all of a sudden, it breaks.  It has to be something with the year
change.  I am working on a fiscal budget from May to May.  I have 19 accounts.
This is a mess!

If you can think of anything else...  I appreciate the suggestions so far.

L

02-Feb-10 02:31 PM

Have you changed calculation mode to "Manual" perchance?


Gord

02-Feb-10 07:18 PM

I do not know where that is - but anything is possible.  Where would I find
Calculation Modes?

02-Feb-10 07:21 PM

OOps.  Sorry.  Found it.  No, they are all on automatic.  No One can figure
this out, including a guy here that teaches Xcel.  (I work at a Univeristy)
This is a mystery.  Wait - rephrase - a FRUSTRATING mystery.