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

[Fleone]

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

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

cheers again in advance
barry

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

[bazza825]

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

[Domenic]

Try...

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

Hope this helps!

In article <[email protected]>,
bazza825 <[email protected]>
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

[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