Solved

Formula stopped working

Posted on 2014-03-15
13
183 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)
ID: 39931951
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_
ID: 39932005
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)
ID: 39932158
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_
ID: 39932190
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)
ID: 39932192
Everything looks good to me. Which cell isn't working and on what sheet?

Kevin
0
 

Author Comment

by:mikecox_
ID: 39932257
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39932259
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
ID: 39932261
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_
ID: 39932746
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_
ID: 39947496
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
ID: 39947761
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_
ID: 39948205
Ok! That explains it.  Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Macro 6 50
Help with Excel formula 6 38
Excel printing page management 2 21
Avoid Multiple Instances of Named Ranges in Excel 3 16
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…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

920 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

16 Experts available now in Live!

Get 1:1 Help Now