#VALUE! displayed when referencing a formula cell
 

I'm using a IF,ISNA,VLOOPUP string in a cell to obtain a price from a
seperate sheet.

However, a seperate cell is set to perform a calculation on this number
that is obtained from the above string.

The problem is that unless the user has entered the required text to
enable the vlookup to obtain the figure, the other column just lists
#value! all the way down...

How do i get it to display and empty cell... I suspect that it's caused
by excel attempting to calculate a result from a formula.. or it that
incorrect.

To summarise as an example
Cell A1, the user enters a search string
Cell A2, contains a Vlookup to search based on A1
Cell A3, contains a calculation that is to be performed on A2
Problem, Where A1 (and therefore A2) dont contain data, A3 displays
#VALUE!

cheers in advance
barry


--
bazza825
------------------------------------------------------------------------
bazza825's Profile: http://www.excelforum.com/member.php...o&userid=33241
View this thread: http://www.excelforum.com/showthread...hreadid=537382

#VALUE! displayed when referencing a formula cell
 

Write your formula in A3 like

=IF(ISERROR(A2),0,VLOOKUP(...))

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"bazza825"
wrote in
message
...

I'm using a IF,ISNA,VLOOPUP string in a cell to obtain a price
from a
seperate sheet.

However, a seperate cell is set to perform a calculation on
this number
that is obtained from the above string.

The problem is that unless the user has entered the required
text to
enable the vlookup to obtain the figure, the other column just
lists
#value! all the way down...

How do i get it to display and empty cell... I suspect that
it's caused
by excel attempting to calculate a result from a formula.. or
it that
incorrect.

To summarise as an example
Cell A1, the user enters a search string
Cell A2, contains a Vlookup to search based on A1
Cell A3, contains a calculation that is to be performed on A2
Problem, Where A1 (and therefore A2) dont contain data, A3
displays
#VALUE!

cheers in advance
barry


--
bazza825
------------------------------------------------------------------------
bazza825's Profile:
http://www.excelforum.com/member.php...o&userid=33241
View this thread:
http://www.excelforum.com/showthread...hreadid=537382

#VALUE! displayed when referencing a formula cell
 

Add a ,"" to the If statement. Something like
IF(A1=0,0,"") this would make the cell that contains it show a 0, or just be
blank depending on what is contained in A1.


"bazza825" wrote:


I'm using a IF,ISNA,VLOOPUP string in a cell to obtain a price from a
seperate sheet.

However, a seperate cell is set to perform a calculation on this number
that is obtained from the above string.

The problem is that unless the user has entered the required text to
enable the vlookup to obtain the figure, the other column just lists
#value! all the way down...

How do i get it to display and empty cell... I suspect that it's caused
by excel attempting to calculate a result from a formula.. or it that
incorrect.

To summarise as an example
Cell A1, the user enters a search string
Cell A2, contains a Vlookup to search based on A1
Cell A3, contains a calculation that is to be performed on A2
Problem, Where A1 (and therefore A2) dont contain data, A3 displays
#VALUE!

cheers in advance
barry


--
bazza825
------------------------------------------------------------------------
bazza825's Profile: http://www.excelforum.com/member.php...o&userid=33241
View this thread: http://www.excelforum.com/showthread...hreadid=537382

#VALUE! displayed when referencing a formula cell
 

"Write your formula in A3 like

=IF(ISERROR(A2),0,VLOOKUP(...))"

sorry, i don't understand this one.. I need this cell to be blank but
when there is a number in A2 i need it to go.. SUM(A2*0.9*0.9) and
display the result.

sorry again, i probably didn't make it clear enough.

The VLOOKUP in A2 works fine and enters the number when required. The
problem occurs because the reference point in the Vlookup is A1, and
when nothings entered there, A2 has nothing to work on (and stays
blank, which it should), but A3 then gives the error because it
probably can't perform a calculation on A2?

i'm getting a headache now :)

cheers
barry


--
bazza825
------------------------------------------------------------------------
bazza825's Profile: http://www.excelforum.com/member.php...o&userid=33241
View this thread: http://www.excelforum.com/showthread...hreadid=537382

#VALUE! displayed when referencing a formula cell
 

Ok, this is what i have in the relevant cells...

D8
=IF(ISNA(VLOOKUP(A8,data!$C$2:$J$2971,3,FALSE)),"" ,VLOOKUP(A8,data!$C$2:$J$2971,3,FALSE))

E8
=SUM(D5*0.9*0.9)

Now if No data is entered in reference cell (A8), E8 just returns
#VALUE!, but i want it blank so the sheet is nice and tidy...
is this possible?

cheers again in advance
barry


--
bazza825
------------------------------------------------------------------------
bazza825's Profile: http://www.excelforum.com/member.php...o&userid=33241
View this thread: http://www.excelforum.com/showthread...hreadid=537382

#VALUE! displayed when referencing a formula cell
 

If I understand your problem correctly, then here what you may want to
do.

You use an IF statement in A2, but not in A3. Put the following in
A3:

=if(iserror(sum(...),"",(sum(...)))

If you don't know what iserror does, look it up in the help. But for
understanding, it tells you whether there is an error or not and the
asnwer it gives if either TRUE or FALSE.

sum(...) is your sum function that you have in A3 now.

Hope that helps.
Proper


--
proper
------------------------------------------------------------------------
proper's Profile: http://www.excelforum.com/member.php...o&userid=33166
View this thread: http://www.excelforum.com/showthread...hreadid=537382

#VALUE! displayed when referencing a formula cell
 

=PRODUCT(D5,0.9,0.9)

ignores text

otherwise

=IF(D5="","",D5*0.9*09)

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"bazza825" wrote in
message ...

Ok, this is what i have in the relevant cells...

D8
=IF(ISNA(VLOOKUP(A8,data!$C$2:$J$2971,3,FALSE)),"" ,VLOOKUP(A8,data!$C$2:$J$2971,3,FALSE))

E8
=SUM(D5*0.9*0.9)

Now if No data is entered in reference cell (A8), E8 just returns
#VALUE!, but i want it blank so the sheet is nice and tidy...
is this possible?

cheers again in advance
barry


--
bazza825
------------------------------------------------------------------------
bazza825's Profile:
http://www.excelforum.com/member.php...o&userid=33241
View this thread: http://www.excelforum.com/showthread...hreadid=537382

#VALUE! displayed when referencing a formula cell
 

proper Wrote:

You use an IF statement in A2, but not in A3. Put the following in
A3:

=if(iserror(sum(...),"",(sum(...)))


I did briefly try that but got the context wrong. So i've tried it
again, as follows..

=if(iserror(sum(d4*0.9*0.9),"",(sum(d4*0.9*0.9))))

But i now get an error message saying that this is wrong... i can't see
where though :((

Damn my stupidity, i bet this is an easy fix as well :(

thanks again
barry


--
bazza825
------------------------------------------------------------------------
bazza825's Profile: http://www.excelforum.com/member.php...o&userid=33241
View this thread: http://www.excelforum.com/showthread...hreadid=537382

#VALUE! displayed when referencing a formula cell
 

Try...

=IF(D4<"",D4*0.9*0.9,"")

Hope this helps!

In article ,
bazza825
wrote:

proper Wrote:

You use an IF statement in A2, but not in A3. Put the following in
A3:

=if(iserror(sum(...),"",(sum(...)))


I did briefly try that but got the context wrong. So i've tried it
again, as follows..

=if(iserror(sum(d4*0.9*0.9),"",(sum(d4*0.9*0.9))))

But i now get an error message saying that this is wrong... i can't see
where though :((

Damn my stupidity, i bet this is an easy fix as well :(

thanks again
barry

#VALUE! displayed when referencing a formula cell
 

Domenic Wrote:
Try...

=IF(D4<"",D4*0.9*0.9,"")

Hope this helps!


....and it has! Thanks a lot for that. It works on any cell with a
similar problem as well., could anyone explain why this works though? I
thought you'd have to use the 'SUM' prefix before the formula..

thanks again
barry


--
bazza825
------------------------------------------------------------------------
bazza825's Profile: http://www.excelforum.com/member.php...o&userid=33241
View this thread: http://www.excelforum.com/showthread...hreadid=537382