Prev: reference sheet number not name
Next: Vlookup, sumproduct, if, sumif... erggg!
From: Gord Dibben on 2 Feb 2010 14:31
>I just don't get it - how could the workbook work perfectly for months and
>then all of a sudden, it breaks

Have you changed calculation mode to "Manual" perchance?


Gord

On Tue, 2 Feb 2010 08:18:01 -0800, lktx <lktx(a)discussions.microsoft.com>
wrote:

>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!!
>> >>
>> >> .
>>
>> .
>>

From: lktx on 2 Feb 2010 19:18
I don't know where that is - but anything is possible. Where would I find
Calculation Modes?

"Gord Dibben" wrote:

> >I just don't get it - how could the workbook work perfectly for months and
> >then all of a sudden, it breaks
>
> Have you changed calculation mode to "Manual" perchance?
>
>
> Gord
>
> On Tue, 2 Feb 2010 08:18:01 -0800, lktx <lktx(a)discussions.microsoft.com>
> wrote:
>
> >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!!
> >> >>
> >> >> .
> >>
> >> .
> >>
>
> .
>
From: lktx on 2 Feb 2010 19:21
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.

"Gord Dibben" wrote:

> >I just don't get it - how could the workbook work perfectly for months and
> >then all of a sudden, it breaks
>
> Have you changed calculation mode to "Manual" perchance?
>
>
> Gord
>
> On Tue, 2 Feb 2010 08:18:01 -0800, lktx <lktx(a)discussions.microsoft.com>
> wrote:
>
> >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!!
> >> >>
> >> >> .
> >>
> >> .
> >>
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: reference sheet number not name
Next: Vlookup, sumproduct, if, sumif... erggg!