MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How Do I Correct a New Numbers File After a New Year?

I have a Numbers table from last year that contains dated entries (increasing dates vertically in the first column, e.g., 1/1/2017). The second column creates month names from those dates (e.g., Monthname(Month(A2)), the third column contains a description of a category, and the final column contains the item cost.

I added a new table whose header row contains month names (January, February, etc.). The first column contains the summary items. Each monthly summary entry uses “sumifs” to select items and sums from the first table: {e.g.,
sumifs($1stTable:Cost, $1stTable:MonthName, ThisTable:$A2,
$1stTable:Description, ThisTable:SummaryName)}

When I added a few new entries this year, my monthly summary table reloaded with zero value entries (there’s no formula error). ThisTable’s header line appears to have correct dates (e.g., 1/1/2017).

When I changed ThisTable’s header line to formulas {e.g.,
MonthName(Month(1/1/2017)}
it works.

What broke?
—–
Ray

Comments: 4 Responses to “How Do I Correct a New Numbers File After a New Year?”

    6 years ago

    Perhaps it is the relative/absolute row and column values in your formulas? It is hard for me to decipher just based on your description, but remember that a $ in the formula before the column or the row means that the column or row is held steady. Without the $, it changes when you copy and paste or add a new row. So $A2 for instance would hold the column steady at A, but the row would change. $A$2 would fix the formula to only look at A2 and never change no matter where you pasted it.

    Ray
    6 years ago

    The tables worked before 2018, then failed after the start of 2018 when I updated the tables, so I think I had the formulas correct. The entries came back after updating the header row. Some of the new entries were in 2018, and for those entries, I changed the MonthName entries to text (still a month name showing as a date, but not a calculation in 1stTable).

    6 years ago

    OK, after examining the file I see what is wrong. The values in the one table are the names of the months, the results from the MONTHNAME function. So literally the string "February." But the column headings in the other table that you are comparing them to are actual dates, like 2/1/2017, but the cells are formatted so as to just show the month. So you are comparing the text "February" with the date 2/1/2017 and they don't match because they are different things. When you wrapped the column head in MONTHNAME(MONTH()) you converted the value from 2/1/2017 to the text "February" so "February" matches "February" and it works.
    What probably happened is that you typed the word February into the column heading and it converted it to a date 2/1/2017. You can check this by looking at the bottom of the Numbers window where it shows you the actual value. You'd see a 2/1/2017 instead of a "February."
    If you format the cells as Text instead of Automatic, it will take you entry of "February" literally and not convert it to a 2/1/2017.

    Ray
    6 years ago

    It looks like a one way conversion, too, Gary,Changing the ThisTable header format back from text to "Automatic" or "Date & Time" also changes the date field to 2018, although the row summaries look correct. then changing the Year-in-date field back to 2017 zeros out those summaries, again.

    Thanks for your help. Numbers mostly does what I want, even when I'm not sure why it's doing it...

Comments Closed.