Calendar Template Curiosity

Question

Level 1

0 points

Calendar Template Curiosity

Hello all. I have been beating my head against the wall with this one. The new Numbers is certainly a mixed bag. Personally I love it but there are some features that I'm sure most of us wait, with bated breath, to be built back into the program.

I learn a lot about the mathemagical inner workings of the spreadsheet by reverse-engineering the Apple-supplied templates to learn good practices and pick up some tips and tricks. But this week I cam across something of a conundrum.

One of the new templates included in both the OS X and iOS versions of Numbers is this Calendar template. It's elegant, simple and the formulae are purposeful. But in picking it apart and rebuilding it I noticed the Month pop-up cell must be formatted in a peculiar way or something. When I set out to remake this sheet, it was one of the first cells I made and formatted. A simple pop-up with the months of the year listed. But as soon as I began typing in formulae, I noticed it produced an error in calculations. I checked and re-checked everything looking for trailing or leading spaces in my value list for the pop-up formatting.

So I performed a simple test.

I made a new table with two pop-up cells. Each with the names of the months; January is listed through December. One is a copy and paste from the original Apple template and the other is my own pop-up. Both are formatted properly and identically. In a cell below I created a formula (IF A1=B1, "Yes", "No") to test the actual values driving the calculation. Without knowing what it is that causes it, the test shows the Apple's January ≠ my January. As soon as I copy and paste another one of Apple's month pop-ups into that cell, then and only then does A1=B1.

Would anyone care to investigate this template and help me understand what is causing the inconsistency?

Posted on Feb 17, 2014 8:39 AM

Reply

Answer 1

Feb 17, 2014 8:50 AM in response to Volvoguy

You should probably unhide the rows that are hidden in the Month Year table (top left):

There are extra calculations in those rows

Reply

Answer 2

Volvoguy Author

Level 1

0 points

Feb 17, 2014 8:55 AM in response to Wayne Contello

Been there, done that.

That's fine. I've seen all the formulae that drive the entire calendar. But in picking it apart and rebuilding it, I discovered that the Month chooser behaves differently. It's the only inconsistency. I made a month picker just like Apple's, formatted as pop-up, font, justification, color, inset margin everything. But when used in the selfsame calculation(s), it produces an error. Copy and paste theirs into my table, and it works.

Reply

Answer 3

Feb 17, 2014 8:55 AM in response to Volvoguy

What is the error message?

Reply

Answer 4

Volvoguy Author

Level 1

0 points

Feb 17, 2014 8:56 AM in response to Wayne Contello

It says LOOKUP couldn't find the requested value.

Reply

Answer 5

Feb 17, 2014 8:59 AM in response to Volvoguy

you should also unhide other columns in the top-left table:

Reply

Answer 6

Volvoguy Author

Level 1

0 points

Feb 17, 2014 9:02 AM in response to Wayne Contello

I have unhid everything. Like I said. I have recreated the entire calendar from the ground up after studying the WHOLE thing; hidden rows and columns et. al. I have a working copy. But it only works because I had to copy Apple's calendar picker and paste it as my own created pop-up of the months produced the Lookup error. That's what I'm trying to figure out.

Reply

Answer 7

Volvoguy Author

Level 1

0 points

Feb 17, 2014 9:05 AM in response to Wayne Contello

In fact, If I make an identical pop-up with the names of the months and paste that into the Apple template, I get the same error. It has to be something with that one cell.

Reply

Answer 8

Feb 17, 2014 9:12 AM in response to Volvoguy

Please email your non-working file. My email is in the "Biography" of my profile

Reply

Answer 9

Volvoguy Author

Level 1

0 points

Feb 17, 2014 9:17 AM in response to Volvoguy

Opened new template.

Substituted my own pop-up Months chooser in for Apple's.

Formulae untouched.

It's that one cell.

Reply

Answer 10

Feb 17, 2014 9:30 AM in response to Volvoguy

The months Apple used have the first letter capitalized and the rest lower case. You seem to have ALL leters of the month Capitalized

Reply

Answer 11

Badunit

Level 7

25,784 points

Feb 17, 2014 10:11 AM in response to Wayne Contello

I am confused at the question and the screenshots. The the screenshots in the first post are not showing up for me and I'm not understanding the sequence of the three most recent screenshots. I don't know where a lookup function is in the template; I don't see which cell would be giving an error about LOOKUP not finding the value.

One thing to watch out for in Numbers is that a month name can get treated as a Date & Time value. Try the formula =A1+1 in the Month&Year table and you'll see the result is a date & time value (it will be the second day of the month in the current year). I don't know if this is related to your problem or not.

One odd thing I noticed when playing around is that you cannot make a popup that has a month name that is not title capitalized. Enter it as all caps or no caps or any other combination other than title capitalization and it switches it to title capitalization.

Reply

Answer 12

Feb 17, 2014 10:12 AM in response to Volvoguy

There is something different between the pop-up menu for the month selection. When I use the clean() function I can get the pop ups to match:

A2=IF(A1=CLEAN(B1),"Yes","No")

I even retyped the pop-up menu on the right in cell B1

Reply

Answer 13

Badunit

Level 7

25,784 points

Feb 17, 2014 11:09 AM in response to Wayne Contello

I think it is the old date & time thing going on. The popup was created in 2013 (or before) so it has an intrinsic year of 2013 (though if you do YEAR it says 2014).

If you edit the popup, change January to Januaryyyy (so it loses all connection to being a date) then back to January, it will equal your new January pop up menu.

If you make both into strings in any formulas, like you did with CLEAN or by tacking on &"", it doesn't use it like a date, it uses it like text

Reply

Answer 14

Feb 17, 2014 11:11 AM in response to Badunit

Badunit,

Nope. That did not help. The OP already narrowed the problem down to a simple test (shown in my previous post). In cell A1 is the pop-up copied from Apples template. In cell B1 is a self-made pop-up formatted to look the same.

Cell A2 has a test to indicate whether the two are equal.

A2=IF(A1=B1,"Yes","No")

Oddly enough they are not. I was able to get them to be the same if I modified the formula to:

A2=IF(A1=CLEAN(B1),"Yes","No")

So there is "stuff" in the self-made pop-up that is not in the Apple-provided pop-up.

Reply

Answer 15

Badunit

Level 7

25,784 points

Feb 17, 2014 11:16 AM in response to Wayne Contello

I did the same test. I copied the popup to another document and made a pop up of my own. I got the same result you got: false when you'd think it should be true.. The I edited the popup from the template, changed January to Januarry then I changed it again back to January. Now the result is TRUE. You have to change the popup twice, ending with hitting the Return key each time.

Reply