Horrible looking IF function....

I using the IF function to enter a certain value from another workbook into
a cell, but if that value happens to be 0, to enter "" (blank). My reference
is quite a long winded expression using VLOOKUP, and I find that I have to
enter the expression twice within the formula, once in the first argument for
IF followed by <>0, and again in the second argument for IF, to show what I
want in the cell if the first condition is true. Basically, something like
this, itself has an IF function and VLOOKUP function amongst others...

=IF([expression]<>0,[expression],"")

Its easy enough to copy and paste the expression, but I end up with a
function that's 3 lines long in the cell window and it looks pretty hairy!
Surely there must be a tidier way of doing this???

Put your VLOOKUP expression in another cell, then reference this with the If
function.

A1: VLOOKUP....
B1: =IF(A1<>0,A1,"")


--
Ian
--
"rmellison" <rmellison@discussions.microsoft.com> wrote in message
news:A8970091-8D6F-4CAD-95D9-FF3C2AE0B5A5@microsoft.com...
>
> I using the IF function to enter a certain value from another workbook
> into
> a cell, but if that value happens to be 0, to enter "" (blank). My
> reference
> is quite a long winded expression using VLOOKUP, and I find that I have to
> enter the expression twice within the formula, once in the first argument
> for
> IF followed by <>0, and again in the second argument for IF, to show what
> I
> want in the cell if the first condition is true. Basically, something like
> this, itself has an IF function and VLOOKUP function amongst others...
>
> =IF([expression]<>0,[expression],"")
>
> Its easy enough to copy and paste the expression, but I end up with a
> function that's 3 lines long in the cell window and it looks pretty hairy!
> Surely there must be a tidier way of doing this???
>
>

Hadn't considered that! Would certainly work, but would double the number of
cells I have, and I have enough as it is! Was wondering if there was a way of
doing it within a formula, in layman's terms, somthing like this:

=[expression], unless equal to 0, then ""

Not critical really, just frustrated by lengthy formulae....


"Ian" wrote:

> Put your VLOOKUP expression in another cell, then reference this with the If
> function.
>
> A1: VLOOKUP....
> B1: =IF(A1<>0,A1,"")
>
>
> --
> Ian
> --
> "rmellison" <rmellison@discussions.microsoft.com> wrote in message
> news:A8970091-8D6F-4CAD-95D9-FF3C2AE0B5A5@microsoft.com...
> >
> > I using the IF function to enter a certain value from another workbook
> > into
> > a cell, but if that value happens to be 0, to enter "" (blank). My
> > reference
> > is quite a long winded expression using VLOOKUP, and I find that I have to
> > enter the expression twice within the formula, once in the first argument
> > for
> > IF followed by <>0, and again in the second argument for IF, to show what
> > I
> > want in the cell if the first condition is true. Basically, something like
> > this, itself has an IF function and VLOOKUP function amongst others...
> >
> > =IF([expression]<>0,[expression],"")
> >
> > Its easy enough to copy and paste the expression, but I end up with a
> > function that's 3 lines long in the cell window and it looks pretty hairy!
> > Surely there must be a tidier way of doing this???
> >
> >
>
>
>

You could always hide the rows or columns with the VLOOKUP in. You'd still
have the cells, but they wouldn't clutter the appearance up.

Alternatively, post the VLOOKUP fromula to see if anyone here can trim it.

--
Ian
--
"rmellison" <rmellison@discussions.microsoft.com> wrote in message
news:145E1487-0282-4478-BE97-7D722DC4B2CE@microsoft.com...
> Hadn't considered that! Would certainly work, but would double the number
> of
> cells I have, and I have enough as it is! Was wondering if there was a way
> of
> doing it within a formula, in layman's terms, somthing like this:
>
> =[expression], unless equal to 0, then ""
>
> Not critical really, just frustrated by lengthy formulae....
>
>
> "Ian" wrote:
>
>> Put your VLOOKUP expression in another cell, then reference this with the
>> If
>> function.
>>
>> A1: VLOOKUP....
>> B1: =IF(A1<>0,A1,"")
>>
>>
>> --
>> Ian
>> --
>> "rmellison" <rmellison@discussions.microsoft.com> wrote in message
>> news:A8970091-8D6F-4CAD-95D9-FF3C2AE0B5A5@microsoft.com...
>> >
>> > I using the IF function to enter a certain value from another workbook
>> > into
>> > a cell, but if that value happens to be 0, to enter "" (blank). My
>> > reference
>> > is quite a long winded expression using VLOOKUP, and I find that I have
>> > to
>> > enter the expression twice within the formula, once in the first
>> > argument
>> > for
>> > IF followed by <>0, and again in the second argument for IF, to show
>> > what
>> > I
>> > want in the cell if the first condition is true. Basically, something
>> > like
>> > this, itself has an IF function and VLOOKUP function amongst others...
>> >
>> > =IF([expression]<>0,[expression],"")
>> >
>> > Its easy enough to copy and paste the expression, but I end up with a
>> > function that's 3 lines long in the cell window and it looks pretty
>> > hairy!
>> > Surely there must be a tidier way of doing this???
>> >
>> >
>>
>>
>>