CHOOSE function not updating automatically

[Drew_]

Hi all.

I am using a CHOOSE function to locate the name of the correct month and "months ending" based on the number month I type in. On my "instructions" sheet, I type in numbers 1-12 in cell K1, and my CHOOSE function on another sheet locates that numbers and returns the month that it corresponds to (i.e. the number 10, function returns "October 31, 2017"). The formula works great, but when I change the number, the formula does not change with it. I have to double click the formula, hit enter, and then it updates. What is the deal with this? Any help would be appreciated, thanks

 

[mse330]

What is your formula ? Is this the only formula in the workbook that doesn't get updated or all formula ? Maybe auto calculate is set to manual ?

 

[Jonmo1]

Sounds like calculation is set to manual.

here's an alternate formula to get a month name from a number 1 to 12..

=TEXT(K1*29,"mmmm")

 

[Rick Rothstein]

I type in numbers 1-12 in cell K1, and my CHOOSE function on another sheet locates that numbers and returns the month that it corresponds to (i.e. the number 10, function returns "October 31, 2017").

If that is what you want outputted (the last day of the month in full out text), you could use this instead of CHOOSE...

=EOMONTH(DATE(YEAR(NOW()),K1,1),0)

This will output the date's serial number... you can format this cell using...

mmmm d, yyyy

that way, the cell will contain a real date that you can calculate off of and yet it will display the date in the format you indicated you want. If you actually need the cell value to be the text for the date (the cell will no longer be a cell you can calculate off of), then use this...

=TEXT(EOMONTH(DATE(YEAR(NOW()),K1,1),0),"mmmm d, yyyy")

Note: You indicated your K1 cell was in a different sheet... you did not tell us its name so I just used K1 by itself... you will have to add the appropriate sheet reference to make the formula work on your computer.

 

[Drew_]

Thank you for the three responses!

Yes, somehow my calculations settings got turned into manual. Not sure how, I never changed it, but I set it to automatic and now it works great! Thank you all