#REF! as result of =Year(d2)

[fri3nd5]

Guys, please, i need your help ...
I have an Excel doc with 4 sheets: start, data, insert, results. Let's pickup for reference cell d2. In in any of these sheets, in any cell, if i insert function "=year(d2)" the result is "#REF!", even if in cell d2 is a date formatted as date: 14 march 2017. IF i delete sheet "data" everything comes back to normal and function year returns the right value (2017). If i delete any of the other sheets but data, nothing changes.
Is must be something wrong with that sheet DATA. But what? Is the first time in my life when this happens to me. Help pleaseeee

 

[Chihiro]

Can you upload your workbook? Without looking at your set up we will be making wild guesses.

 

[fri3nd5]

Can you upload your workbook? Without looking at your set up we will be making wild guesses.[/quote
Send me your e-mail please because the file is to large to be uploaded (3 mb)

 

[Chihiro]

Trim the data down to only enough info to demonstrate your issue. And save it as binary format (xlsb). That should reduce size enough for you to upload.

 

[fri3nd5]

Can you upload your workbook? Without looking at your set up we will be making wild guesses.

Trim the data down to only enough info to demonstrate your issue. And save it as binary format (xlsb). That should reduce size enough for you to upload.

I deleted most of the sheets (i left only "data" and "sheet1") ... i save it a xlsb as you said and is still 1.05 mb ... the doc is almost empty ... but still the size is very large

i also selected all cells but the ones which had data (ctrl+end) and i cleared them but no change ...

 

[Khalid NGO]

Hi @fri3nd5

I have sent you my email address in PM.

You can also use any compression tool (e.g. Win Rar/Zip) and then try uploading.

Regards,

 

[fri3nd5]

Hi @fri3nd5

I have sent you my email address in PM.

You can also use any compression tool (e.g. Win Rar/Zip) and then try uploading.

Regards,

I deleted most of the sheets (i left only "data" and "sheet1") ... i save it a xlsb as you said and is still 1.05 mb ... the doc is almost empty ... but still the size is very large

i also selected all cells but the ones which had data (ctrl+end) and i cleared them but no change ...

Hi @fri3nd5

I have sent you my email address in PM.

You can also use any compression tool (e.g. Win Rar/Zip) and then try uploading.

Regards,

it worked ... i managed to reduce the size with win rar. thanks!

 

[Khalid NGO]

Hi,

I think the problem is related to the named formulas.
Try renaming all the named formulas for example YEAR to YEAR1

Then use Year function in any cell.

Regards,

 

[fri3nd5]

Hi,

I think the problem is related to the named formulas.
Try renaming all the named formulas for example YEAR to YEAR1

Then use Year function in any cell.

Regards,

That was the problem ... the name of the table was year and excel didn't know is i make reference to function year or table year ... i renamed the table and everything started working normally ... thanks!

 

[Khalid NGO]

Hi @fri3nd5
Glad it helped.

Thanks for the feedback.
Take Care