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

0
Utf
1/28/2010 11:48:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

7 Replies
921 Views

Similar Articles

[PageSpeed] 51

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

0
Gord
1/29/2010 12:14:36 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

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

0
Utf
2/1/2010 3:25:01 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

On Mon, 1 Feb 2010 07:25:01 -0800, lktx <lktx@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@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!!
>> 
>> .

0
Gord
2/1/2010 7:41:06 PM
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@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@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!!
> >> 
> >> .
> 
> .
> 
0
Utf
2/2/2010 4:18:01 PM
>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@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@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@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!!
>> >> 
>> >> .
>> 
>> .
>> 

0
Gord
2/2/2010 7:31:55 PM
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@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@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@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!!
> >> >> 
> >> >> .
> >> 
> >> .
> >> 
> 
> .
> 
0
Utf
2/3/2010 12:18:15 AM
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@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@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@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!!
> >> >> 
> >> >> .
> >> 
> >> .
> >> 
> 
> .
> 
0
Utf
2/3/2010 12:21:02 AM
Reply:

Similar Artilces:

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

a simple math formula
Hi, I'm new to this and have a very simple question. I have values, simple numbers representing weight in kg, that I wish to automatically convert to US pounds. The 'kg' numbers are in fields B5 - to B77 for example. I want the conversion result (simply multiplying each B cell entry by 2.2) placed in the ''cell adjacent. Thanks! Dave Horne Hi David In C5 enter =CONVERT(B5,"kg","lbm") and copy down through C6:C77 -- Regards Roger Govier "Dave Horne" <davehorne@home.nl> wrote in message news:upOf6pgUJHA.4916@TK2MSFTNGP06.p...

What is 'Align Text to Base Line Guide'?
Trying to make sure all lines have the same amount of space between them I selected paragraph from the format menu and under the line adjustments there is a box 'align text to base line guides' Ok I did that, now there are lines at the margines of my newsletter. What are these? Also selecting this pushed my text down from the top edge of the text box. What is this? The baseline guide measurements are in the Arrange, Layout Guides, Baseline tab. There is help in the Help menu. -- Mary Sauer http://msauer.mvps.org/ "Go_Girl3647" <GoGirl3647@discussions.microsoft.com...

Array Formula #7
I have an array formula that works correct up to the 20th row. It quit running on row 20 or it does not pick up any information after row 20. Any help out ther -- pete576 ----------------------------------------------------------------------- pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961 View this thread: http://www.excelforum.com/showthread.php?threadid=49427 Rather impossible to tell... What's the formula, and what cell(s) is it entered into? What type of data is in any referenced ranges? In article <pete5761.205n5a_1134773401.141...

Line Chart with dates in 5 day working week only
Hi, Trying to format a chart so that only the 5 working days of the week are displayed on the x axis. The source data only has the five days (e.g. 05/09/2005 down to 09/09/2005 and then on to 12/09/2005 down to 16/09/2005 etc etc) So I have missed out the weekend dates. When I create the line chart however, the weekend dates appear automatically and just show no point on the chart, therefore there is a longer line between Fridays and Mondays!! Hope this makes sense. Does anyone have any ideas on how to change this? I have tried looking at Tools-options-chart and cannot seem to turn...

Labels in Formulas requires manual refresh?
I use Labels in Formulas extensively. (Excel 2003 11.5612.5606) That is, I tick off Options/Calculations/"Accept Labels in Formulas" and then use whatever text string I've placed at the top of a column of data to refer to it. It has many benefits over Named Ranges, dynamic or otherwise for my purposes, but one ongoing annoyance. When I add data to the bottom of the column of data, the in-cell formula using the label for reference does not change when it should. Workaround: Select cell, F2 or double click to open it, Enter. I have a number of such formulae so updating can be ...

Scheduling formula question
I know both are the same equation. Which one is by definition ? "Duration = Work / Units" or "Work = Duration x Units". I have a three day task with a resource assigned (Max. Units 100%, Units:100%). All calendars are the default Standard base calendar; Hours per day is 9 hours. How do we build the equation to calculate 27 hours of work ? TBol -- To be technically correct, the Duration Equation formula is written as: Duration = Work/(Hours Per Day x Units) You find the Hours Per Day value on the Calendar page of the Options dialog, accessed by clic...

Inserting Excel into Access Reports
Office XP Have a great Access application that produces a nice template (headers & footers) report into which I'd like a spreadsheet inserted before going to the printer. In the past, I'd just print the Access reports, then reload them into the inkjet printer and run the Excel spreadsheets as needed. The heat of the new color laserjet turns the paper grey if it runs through too often, so it's time to get the reports printing on one pass. Any suggestions would be welcome. I've of course also got Word XP, MS Publisher XP, as well as Adobe Acrobat, if anyone thinks it m...

copy-paste from excel to powerpoint
Office 97 When I copy a number of cells from Excel to powerpoint, I can't get rid of the grid lines. There are no borders. When I'm in Excel, I see the faint grid lines as you normally would. Unfortunately, these lines also display in powerpoint. How do I stop that behaviour. Thanks so much for any help. Diana Select all the cells you are copying. Then: Format > Cells... > Patterns Then select white color ( bottom right) When the backgound color is set the gridlines vanish unless borders are turned on -- Gary''s Student "Cowtoon" wrote: > Off...

Page break preview & blue line
I using Excel 2003 In page break preview I have always do some thing wrong I'm just pull the blue line by using mouse all page was destroyed It there any tips to return the blue line to the Default pages size setting As far as I know the 'default' print area is all the data, and once you have changed the print area and saved the file you can not get it to go back to the previous setting. If you have problems with this or any other proceedure do try to remember to save your file before you do the awkward bit and then you can go back to the saved copy it it does not work. ...

Need help on a formula 05-20-10
I need a formula to calculate the following information please: I will have someone enter a time (ex 6:31) in cell C3. If the time matches one of the times in cells B17-B22 I need it to display 4.6, if it matched one of the times in cells B23-28 I need it to display 4.7, if it matches one of the times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a simple formula I can do for this please? =IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no match"))) Can't do the "and so on" part, bu...

How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

Delete contents deletes all data and formulas
When I hit delete contents all data and formulas are deleted. How can I delete data without deleting formulas? Hi, You could try this tap F5 - Special - Constants - OK and if that selects the data you want to delete then tap the delete key -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tdhcrr" wrote: > When I hit delete contents all data and formulas are deleted. How can I > delete data without deleting formulas? First use Find...

Excel Formula Help
I am setting up a basic excel spreadsheet and really have got no experience with excel. I want to do something real basic like if A2=laptop display $10 or if A2=desktop display $20 how do I do this? I think that if the list of options is more than a just a few, a =vlookup() function would work nicely. It may seem a little complex to start, but once you use it, you'll find tons of more reasons to use it. Debra Dalgleish has some nice instructions at: http://contextures.com/xlFunctions02.html BadSector wrote: > > I am setting up a basic excel spreadsheet and really have got no...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...

how to insert borders on flyer within Microsoft publisher?
Please help, I'm trying to insert a border around a flyer...I'm using Microsoft Publisher. Thanks, Harriet Is it a clipart border, Borderart or a simple rectangle? What problems are you having? What version Publisher? Any border you insert should be sent to the back so it does not interfere with your main design. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Harriet" <Harriet@discussions.microsoft.com> wrote in message news:7E2ED4D8-CC09-497F-A17C-44AE41F951C9@microsoft.com... > Please help, I'm ...

Sequentially number lines automatically
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How can automatically number the lines as I enter data. <br><br>so box A1 would automatically be 1 <br> and the next time I created an entry on another line that line would be 2, etc. <br><br>The reason is so that I can sort, delete and the numbers will remain, so I can also go back to the order than things were entered into. <br><br>Could do this manually, but a pain. <br><br>Or could possibly do this with a date and time? <br><br>Thanks Steevee Se...

Error trying to make an account default
I have a new computer and my outlook 2003 is not letting me assign a default account to send and receive. I get this error: "The Specicied Account could not be found. It might have been deleted." I have four accounnts setup and have tried removing the accounts and recreating them and still get the error. when the account has been made, it tests fine. None of the four can be made default. Thanks in Advance! ...

IE 6/ActiveX assert in CTLNOWND.CPP line 305
Hello, I have a problem with an ActiveX inside a web page. A random ASSERT in CTLNOWND.CPP line 305 occours during a GetDC first call. It's seems wnd handle is still NULL (it begin valid immediatly after). If I retard calling putting a button in html page it work. CDC* COleControl::GetDC(LPCRECT lprcRect, DWORD dwFlags) { ASSERT((m_hWnd != NULL) || (m_bInPlaceSiteWndless && m_bInPlaceActive)); ..... This problem occours random (more times on faster machines and in release mode) and just on "init" time on my control. Probably my control does this operation in ...

creating nested formulas from drop down box
Hi, I have cell A1 with a drop down box containing 26 available choices. B1 has the dollar amount matching to the choice in A1 using vlookup. E1 totals several cells including B1 together. I want F1 to look at A1 and either enter the number from E1 or NA. Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 (total of b-d1) F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="...

How do I make X-values of a chart dependent on values in cells?
Greetings. I have a chart which can go from x-value 0 to x-value 200. However I'd like to be able to input min X-value into a cell, and a max X-valu into a cell, and the x-value in the chart changes to reflect that. Is it possible to do that? Thanks for any replies. K -- Message posted from http://www.ExcelForum.com Hi, There is no automatic way to do this but take a look a Tushar's AutoChart Manager for a possible solution. (http://www.tushar-mehta.com/) Cheers Andy Kashgarinn < wrote: > Greetings. > > I have a chart which can go from x-value 0 to x-value 200...

Using BGInfo Command Line Parameters
I've read the Help file in BGInfo - but can't figure out how to use Command Line Parameters. When I click on my startup icon, I would like to invoke certain parameters. The line I came up with is: "C:\Program Files\BGInfo\BgInfo\Bginfo.exe /timer:99 /popup /taskbar /rtf:C:\DATA\BGINFO /nolicprompt" Obviously, this is wrong. My Target line reads: "C:\Program Files\BGInfo\BgInfo\Bginfo.exe" My Start in line reads: "C:\Program Files\BGInfo\BgInfo" Please help me by telling me what, exactly, should go in each line. I would also like t...

Make sales target (quota) invisible for other users
I am setting up a MS CRM system for a pilot which focus primarily on our sales department. I have given each user who's a salesperson given a quota in the CRM system. These numbers are confidential and so not everybody may be given the possibility to view this data. But when I checked with another role, I could see the quota of erverybody who's got quota assigned. Is there a possibility to make these quota's only visible to the persons I want (for example the director can see the quota's of everybody, the salespersons can only see the quota for themselves and the rest o...

passeord Protect for excel formula and VBA code
Hi, i have excel sheet its contain lot of formula and VBA(macro) code, its for used for user purpose, i need how to protect the formula and VBA at the time of user using. kindly let me know , kindly help me out i need user password : for user can upload the data in non restriction cell, admin password : can change any thing(change power) pls help me, Please ask on the Excel board. "deen" wrote: > Hi, > > i have excel sheet its contain lot of formula and VBA(macro) code, > > its for used for user purpose, > > i need how to protect the formula and ...

Can't Make Brochures
To make a brochure, I open up Publisher, go to 'Publications for Print' -> 'Brochures'. No matter which design I select, about 5 seconds after the 'Changing your Publication' window comes up, another window pops up telling me 'Publisher cannot complete the operation'. This only happens for brochures - all other publications work. Does anyone have the answer? Try a detect and repair in the Help menu. Do a disk clean-up. Don't delete the Office setup files if you do the disk clean-up. The clean-up is found by right clicking your hard drive, propertie...