Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Inserting Lines Makes Formula Inactive

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Inserting Lines Makes Formula Inactive

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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Inserting Lines Makes Formula Inactive

http://office.microsoft.com/en-us/te...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
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!!


.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Inserting Lines Makes Formula Inactive

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.

InsertTableCreate 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 ToolsDesign.

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
wrote:

http://office.microsoft.com/en-us/te...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
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!!


.


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Inserting Lines Makes Formula Inactive

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.

InsertTableCreate 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 ToolsDesign.

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
wrote:

http://office.microsoft.com/en-us/te...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
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!!

.


.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Inserting Lines Makes Formula Inactive

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

InsertTableCreate 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 ToolsDesign.

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
wrote:

http://office.microsoft.com/en-us/te...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
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!!

.


.


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Inserting Lines Makes Formula Inactive

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

InsertTableCreate 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 ToolsDesign.

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
wrote:

http://office.microsoft.com/en-us/te...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
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!!

.

.


.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Inserting Lines Makes Formula Inactive

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

InsertTableCreate 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 ToolsDesign.

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
wrote:

http://office.microsoft.com/en-us/te...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
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!!

.

.


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting PDF makes file huge Art Excel Discussion (Misc queries) 0 December 17th 08 04:49 PM
Inserting worksheet makes formulae stop working Mark Excel Discussion (Misc queries) 5 April 24th 08 07:10 PM
Inserting Text Makes Numbers Disappear! al Excel Discussion (Misc queries) 5 January 5th 08 03:38 PM
HELP PLEASE..Inserting combobox (ActiveX control) makes workbook c Inquiringmind Excel Discussion (Misc queries) 0 February 10th 07 12:01 PM
Inserting Lines or Copying lines with formulas but without data wnfisba Excel Discussion (Misc queries) 2 August 18th 06 04:41 PM


All times are GMT +1. The time now is 09:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"