On column C, row 5 dated as "10/09/2010" On column D, row 5 used formula is "=week"

I hve defined a Name Manager formula to work "=week" formula -

IF(DATA!$C5="","","WEEK-"&WEEKNUM(DATA!$C5)-1)Note: this condition will work at any cell of the workbook and its working great.

My query when i re-opening the file for third time i getting a value as "#VALUE!" in that particular cell, Pls assist me on this.

Your formula will work as long as the data in cell C5 is entered as a real date, ie 10/9/2010If you enter the date surrounded with quotes, ie "10/9/2010", as shown in your message, then you will get the VALUE error.

MIKE

Hi, I presume it was the cell containing =week that showed #VALUE

If you enter the formula =IF(DATA!$C5="","","WEEK-"&WEEKNUM(DATA!$C5)-1)

in another cell, at the same time as =week shows the #VALUE, does the cell containing the full formula also show #VALUE, or does it give a WEEK-xx message.If it gives the #VALUE error, use formula auditing to see where the error is occurring.

Select the cell then:

In Excel 2003 goto 'Tools' - 'Formula Auditing' and select 'Evaluate Formula'

In Excel 2007/2010 from the Ribbon select 'Formulas' then 'Evaluate Formula' in the 'Formula Auditing' section.Then in the Evaluate Formulas dialog box, click Evaluate.

Look at the part of the formula that is underlined, and this is what will be evaluated at the next click of the 'Evaluate' button.Between these two items - comparing the result of the full formula and formula evaluation - it should narrow down the source of the problem.

Let us know what you get.

Regards

I set up a workbook with the data and formula from your post on 2 different machines running 2003, opened and closed the files numerous times, and never had a problem. Do what I did:

Copy and paste the items from your post into a worksheet. Since they worked fine for me, they should work fine for you. ;-)

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History