Formula stopped working

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
mikecox_Asked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
mikecox_Author Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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

Kevin
0
 
mikecox_Author Commented:
That's what I was afraid of.

Here is the workbook; will that help?

Master-Budget-FF.xlsm
0
 
zorvek (Kevin Jones)ConsultantCommented:
Everything looks good to me. Which cell isn't working and on what sheet?

Kevin
0
 
mikecox_Author Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Got it!

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

Kevin
0
 
mikecox_Author Commented:
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
 
mikecox_Author Commented:
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
 
mikecox_Author Commented:
Ok! That explains it.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.