Formula via Define Name (not working)
 

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)

Formula via Define Name (not working)
 

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)

Formula via Define Name (not working)
 

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

Formula via Define Name (not working)
 

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