Results 1 to 12 of 12
  1. #1
    Kurt
    Guest

    How to hide 0 value in index match...

    hi all,

    i'm using index match to find
    some data in my database
    yhe problem is, when there's no data
    in my database, the cell that have
    the indexmatch formula will show
    the 0 value, how can hide
    this 0 value so when i print it,
    the 0 value will invisible.
    thanks

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to hide 0 value in index match...

    Kurt,

    Write some code for the worksheet that changes the foreground color of all cells with "0" to match the background color, or you can specify which cells you would like to change. for example,

    sub worksheetinit()
    if Range("A" & 1).value = 0 then
    Range("A1").Select
    Selection.Font.ColorIndex = 2
    else
    end sub
    endif
    end sub

    That should work, I think..
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to hide 0 value in index match...

    Since you did not tell us what your current formula is, I will just refer to it as YF in the formual below. In the formula below, insert everything AFTER the equal sign in your current formula in boths places where YF appears.

    =If((YF)=0,"",YF)
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to hide 0 value in index match...

    Go to Tools>Options, click on the View tab, and turn off Zero Values. This will show blank in the sheet wherever you have a zero value.

    This will only apply to the sheet that is active when you set the option. If you want it on more than one sheet, select all the sheets first.

    Jon

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to hide 0 value in index match...

    I'd have to say, I like Jon's idea best of the three... taking the 'more obvious' route.. bravo! [img]/w3timages/icons/smile.gif[/img][img]/w3timages/icons/smile.gif[/img]
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Kurt
    Guest

    Re: How to hide 0 value in index match...

    Wow..gee...u guys are super fast.thanks to
    all the response..but i hate to admit....
    hehe..but Jon idea is best the solution..cheers
    thanks to all of u too..

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to hide 0 value in index match...

    It's because I'm lazy. Why write more code than I have to?

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to hide 0 value in index match...

    Jon: Using your method is easy, but it will hide ALL zero values on the sheet. The original question said only wanted to hide the zero value from one formula. If he doesn't mind hiding all zeros, then your solution will work.
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to hide 0 value in index match...

    Another way for hiding zeroes just in some cells is to apply custome formatting to those cells:
    0;;0
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  10. #10
    Kurt
    Guest

    Re: How to hide 0 value in index match...

    Hi again,

    this is interesting..could u define it more details
    on this conditional formatting..includes its full
    formula..thanks....

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to hide 0 value in index match...

    That was not a conditional format, it was a custom format. However, the format he gave was incorrect. The format he gave will display positive integers and zero, but will display nothing for negative numbers. It should have been:

    0;-0;

    That format will display positive and negative integers but nothing for zero values. It would have to be modified for decimal places and thousands separators if you need either of those.

    You could also use a conditional format to do this. The condition would simply say if the cell value is zero, set the font color to the background color.

    I still prefer using the IF formula to accomplish this because it does not stop working if the user selects a column or row and changes the number format or background color. It also works on all versions of Excel which the conditional formating does not.
    Legare Coleman

  12. #12
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to hide 0 value in index match...

    Legare,

    Thanks for the pickup on formatting numbers. Duh.

    Conditional formatting is nice. I've used in sometimes to hide values as you suggest- but I got caught out once when somebode copied and pasted data into antother application- the previously "hidden" value showed up. I use it more often to highlight values out of the normal- eg, stock prices changed above a certain percentage get shown in red; over a larger percentage, also in bold.

    As far as custom formatting vs changing the formula- they both have their places. As Legare says, it's much easier for a user to change formatting than to change a formula. But a cell which contains an empty string rather than a zero formatted to cannot be used in further arithmetic.


    Kurt- some explantion of custom formats (forgive me if you already know this):

    To apply it, select your cells; then select Format, Cells; and select "Custom" from the category. Then under "Type" enter in your formatting. Entering three formats between semi-colons applies to first format to positive numbers, the second to negative numbers, and the third to zero values.

    So a format of:
    0;-0;
    applies formatting of whole numbers (no commas or decimal places) to any positive number; the same formatting preceded by a minus sign to any negative number; and blank to any zero number.

    A format of
    "A";"B";"C"
    would substitute (in the display- the underlying value would be unchanged) the strin "A" for positive numbers, "B" for negative, and "C" for zero (not very useful.

    There are a series of custom formats which you can pick from the list- that will give some ideas how you can use them. Also read the help- that's always a good place to go.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •