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.

Whats wrong?

--
Edmund
(Using Excel XP)

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(INDIRE CT("Sheet3!C"&ROW())<0.12,"C","B"))

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


--
Edmund
(Using Excel XP)


"Edmund" wrote:

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.

Whats wrong?

--
Edmund
(Using Excel XP)

It appears to work whether nameg Test or any other name. Have you tried
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 your
worksheet and tried with another name?

--

Edmund Wrote:
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(INDIRE CT("Sheet3!C"&ROW())<0.12,"C","B"))

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


--
Edmund
(Using Excel XP)


"Edmund" wrote:

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.

Whats wrong?

--
Edmund
(Using Excel XP)


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=542048

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.

--
Edmund
(Using Excel XP)


"Bryan Hessey" wrote:


It appears to work whether nameg Test or any other name. Have you tried
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 your
worksheet and tried with another name?

--

Edmund Wrote:
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(INDIRE CT("Sheet3!C"&ROW())<0.12,"C","B"))

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


--
Edmund
(Using Excel XP)


"Edmund" wrote:

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
(Using Excel XP)


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=542048