Formula via Define Name (not working)

[Edmund]

I always define name for my formulas. They all worked well except this time.

As usual, using the menu bar (Insert | Name | Define), I gave the below
formula a name called TEST in the “Names in workbook:†inputbox within the
Define Name dialog box.

Refers To:
=IF(INDIRECT("C"&ROW())>=0.65,"A",IF(INDIRECT("C"&ROW())<0.12,"C","B"))


Just like all other named formula, I would enter in a cell of its workbook
by preceeding the name with an equal sign =TEST. However, I get it returning
#VALUE! this time.

What’s wrong?

 

[Edmund]

I think I knew why the earlier method did not suceed as I had left out the
sheet reference. Sheet3 is where I use the named formula.

But even when I rectify the named formula by adding in the text "Sheet3!"
within the formula, still it returned #VALUE!
=IF(INDIRECT("Sheet3!C"&ROW())>=0.65,"A",IF(INDIRECT("Sheet3!C"&ROW())<0.12,"C","B"))

I just don't understand what I'd done wrong.

 

[Bryan Hessey]

It appears to work whether nameg Test or any other name. Have you trie
cllosing all other workbooks to test? Do you have a Sheet3? Does C(Row
have a valid value? Have you copied the formula from here back to you
worksheet and tried with another name?

--

 

[Edmund]

Shee3 do exist. Infact colum C in Sheet3 holds the standard cost. Column C is
formatted General (absolutely without any currency nor decimal formatting).
This I'm very sure. Sheet3 also contains >5000 unique part numbers all in Col
A while Col B holds its description. Nevertheless the named formula does not
refer to any other columns in Sheet3 but Column C only.

I just can't understand what is wrong. Absolutely identical formula used but
....... one returning #VALUE! when being defined a name, while the other
returns a perfect answer when entered directly into any cell from Col D
onwards.

There's absolutely no macro in the workbook. Closing all other workbooks,
made no difference either.

I just can't understand what is wrong.