#VALUE! displayed when referencing a formula cell

[bazza825]

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

 

[Chip Pearson]

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"
message
news:[email protected]...

 

[Guest]

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]

"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]

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 return
#VALUE!, but i want it blank so the sheet is nice and tidy...
is this possible?

cheers again in advance
barr

 

[proper]

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

 

[Peo Sjoblom]

=PRODUCT(D5,0.9,0.9)

ignores text

otherwise

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

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[bazza825]

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

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

I did briefly try that but got the context wrong. So i've tried i
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 se
where though (

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

thanks again
barr

 

[Domenic]

Try...

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

Hope this helps!

 

[bazza825]

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