Solved

Formula stopped working

Posted on 2014-03-15
13
182 Views
Last Modified: 2014-03-22
I have a budget workbook that has a List and a Details sheet.  When I enter an amount in the Details sheet it sums the amount in the cell in the main sheet.

I could attach the entire workbook if this attachment doesn't offer enough clues.
 List I'm not sure if it's the code or if there is something wrong with this List sheet;
List
0
Comment
Question by:mikecox_
  • 6
  • 6
13 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
If worksheet cells do not seem to be calculating when changing worksheet cell values or copying cells containing formulas then workbook or worksheet calculation is turned off. To turn on automatic calculation in Excel 2007 and later, click on the Office Button or File tab, select Options, select Formulas, and set Workbook Calculation to Automatic. In Excel 2003 and earlier the menu command Tools->Options, navigate to the Calculation tab, select Automatic, and click OK.

Note that in some cases manual calculation may make more sense such as when there are many calculations, complex calculations, or many UDF references which cause every calculation to take more than a second or two.

Note that a workbook can be manually calculated at any time by pressing F9.

Kevin
0
 

Author Comment

by:mikecox_
Comment Utility
I don't believe this is an issue because all other entries made in the Details sheet appear in the Mater sheet instantly. This formula is used in about 5 other rows and is working perfectly; can't figure out if the formula is messed up; because it don't understand it, so can't trouble shoot it,  

It is just in this one row of cells where this formula appears to not work.  

The question is; is it the formula, or the "list" sheet, or something about this "Details"  sheet; where the entries are made?
Details sheet
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
It's impossible to tell given the limited amount of information. There is a lot that is not shown.

Kevin
0
 

Author Comment

by:mikecox_
Comment Utility
That's what I was afraid of.

Here is the workbook; will that help?

Master-Budget-FF.xlsm
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
Everything looks good to me. Which cell isn't working and on what sheet?

Kevin
0
 

Author Comment

by:mikecox_
Comment Utility
Enter a value on the Details sheet; use the Late fee column, for any date in the fiscal year.  That value will not be reflected on the Master sheet, in the Late fee column associated with that date. The formula in that row is supposed to cause the value to show up there, and be summed if another value is entered in the same month.

Do the same for Misc, Laundry, Landscaping and Building; those cells have the same similar formula, and the amount will be reflected on the Master sheet, in the column associated with the entered date.

When you add another value for a given date the amount is summed.  The cells with that formula work in all the cells with that formula except the one in LATE fee etc.

Is there something wrong with that formula, or is there something wrong with the Details or Lists sheets?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
I'm not following.

Please be more specific. Enter a value in what cell(s) and what cell(s) are not calculating? Use terminology like "Enter a value in cell A1 on Sheet 1. I expect to see the value in cell A1 on Sheet 2 to show 123 instead of 0."

Kevin
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 500 total points
Comment Utility
Got it!

Change the value in cell A14 on sheet "Master" from "LATE Fee etc." to "LATE Fee etc" (remove the period.)

Kevin
0
 

Author Comment

by:mikecox_
Comment Utility
Opps; it only seems to be working with the 2013 entry.  The entry for 1/15/2014 is not working (-:

Now figure that one out!

Note: I added a value in all cells in the LATE row, one for each month.  Notice how only a few got the entry.

Master-Budget-trials.xlsm
0
 

Author Comment

by:mikecox_
Comment Utility
I will give "best solution" to anyone who can tell me why  the "Late free etc" formula does not copy the ranges, incrementally, across the entire row.

In order to fix this problem I dragged the formula across the row; but the ranges did not change to match the respective columns; which, I discovered, is why the formula wasn't working.

I had to go into each cell in the row and change the ranges manually; to match the row it was in.
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
Comment Utility
Excel table references are not treated the same as A1 references. There are some oddities and you ran into one. When copying a formula with structured table references, Excel treats the references one of two different ways depending on how the copy is performed. If you drag the formula (fill) Excel adjusts the references - the reference is shifted to the next or previous table column depending on the direction of the fill. If the reference is that last or first column then it loops around to the other side of the table - pretty silly if you think about it ;-) If you copy and paste the formula Excel doesn't change the table references.

To solve your problem, select cell D14, press CTRL+C, select cells E14:N14, and press CTRL+V.

It's one of those oddities that we think should be resolved by Microsoft.

Kevin
0
 

Author Closing Comment

by:mikecox_
Comment Utility
Ok! That explains it.  Thanks.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now