1 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

MONTH() and YEAR() functions return #REF, and the "Month" and "Year" filters on pivot tables seem to be the issue.

Our take

It sounds like you're encountering a common issue where the MONTH() and YEAR() functions are being interpreted as structured references in your specific sheet, leading to the #REF error. This often occurs when Excel prioritizes structured references over standard functions. Since these functions work correctly in a new file, you may need to ensure that your syntax is clear and that Excel recognizes your intent. Try prefixing your functions with an equal sign and parentheses, like “=MONTH(<cell reference>)”, to help distinguish them from structured references.

I am trying to use the MONTH() and YEAR() functions, but whenever I type them in this specific sheet, they seem to autocorrect to "Month" and "Year" (the first structured reference option in the select drop down that appears as you type) and produce an error, even when the function option is explicitly selected. Typing "=Month" alone produces an array of dated data from elsewhere in the sheet, which seems to indicate that it's reading it as a structured reference instead of a function. The functions work normally in a fresh file (even when referencing the same data in the original file). Is there a way to indicate that is should be a function, and not a structured reference?

submitted by /u/Acolyte_of_Blucifer
[link] [comments]

Read on the original site

Open the publisher's page for the full experience

View original article

Tagged with

#Excel alternatives for data analysis#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#rows.com#Excel compatibility#Excel alternatives#MONTH#YEAR#functions#structured reference##REF#pivot tables