Solved

Formula stopped working

Posted on 2014-03-15
13
188 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_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

739 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