Prev: reference sheet number not name
Next: Vlookup, sumproduct, if, sumif... erggg!
From: lktx on 28 Jan 2010 18:48
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 can't
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 can't figure out.

HELP PLEASE! The invoices are piling up!!

From: Gord Dibben on 28 Jan 2010 19:14
Please post the URL for download.

I can have a look at it.


Gord Dibben MS Excel MVP

On Thu, 28 Jan 2010 15:48:02 -0800, lktx <lktx(a)discussions.microsoft.com>
wrote:

>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 can't
>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 can't figure out.
>
>HELP PLEASE! The invoices are piling up!!

From: lktx on 1 Feb 2010 10:25
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

"Gord Dibben" wrote:

> Please post the URL for download.
>
> I can have a look at it.
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 28 Jan 2010 15:48:02 -0800, lktx <lktx(a)discussions.microsoft.com>
> wrote:
>
> >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 can't
> >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 can't figure out.
> >
> >HELP PLEASE! The invoices are piling up!!
>
> .

From: Gord Dibben on 1 Feb 2010 14:41
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

On Mon, 1 Feb 2010 07:25:01 -0800, lktx <lktx(a)discussions.microsoft.com>
wrote:

>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
>
>"Gord Dibben" wrote:
>
>> Please post the URL for download.
>>
>> I can have a look at it.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Thu, 28 Jan 2010 15:48:02 -0800, lktx <lktx(a)discussions.microsoft.com>
>> wrote:
>>
>> >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 can't
>> >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 can't figure out.
>> >
>> >HELP PLEASE! The invoices are piling up!!
>>
>> .

From: lktx on 2 Feb 2010 11:18
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 don't 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'm 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


"Gord Dibben" wrote:

> 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
>
> On Mon, 1 Feb 2010 07:25:01 -0800, lktx <lktx(a)discussions.microsoft.com>
> wrote:
>
> >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
> >
> >"Gord Dibben" wrote:
> >
> >> Please post the URL for download.
> >>
> >> I can have a look at it.
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >> On Thu, 28 Jan 2010 15:48:02 -0800, lktx <lktx(a)discussions.microsoft.com>
> >> wrote:
> >>
> >> >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 can't
> >> >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 can't figure out.
> >> >
> >> >HELP PLEASE! The invoices are piling up!!
> >>
> >> .
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: reference sheet number not name
Next: Vlookup, sumproduct, if, sumif... erggg!