Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hiding #Value! (2003 SP2)

    Good morning

    I have a number of cells that show #Value! until other cells on other sheets are completed and I would like to hide them, in Conditional Formatting I have =ISERROR($D$10:$F$29) and have set the font colour to grey to match the background colour that I am using but the #Value! still shows up in black until the other cells on other sheets have been filled in. I am obviousely making a stupid mistake but I just cannot see where, any ideas please?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hiding #Value! (2003 SP2)

    Hi Steve,

    Do you want Conditional Formatting to hide the errors? You can use the IF(ISERROR(...)) function to hide the #Value. You will need to build it around the functions taht generate the #Value, similar to : =IF(ISERROR(Formula generating errror),"",Formula generating error)

    PS: Make sure that you do not use absolute references in the rule in Conditional Formatting: Try : =ISERROR(D10:F29)
    Regards,
    Rudi

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding #Value! (2003 SP2)

    Thanks Rudi

    I will go away and give this a try

    Nice new avatar for the new year, any significance in an owl (I thought it might have been a sparrow!!)

    My dear old dad used to always say this poem about owls when we spoke foolishly or out of turn (I obviousely heard it a lot!!!)

    A wise old owl sat in an oak
    the more he heard the less he spoke
    the less he spoke the more he heard
    why can't we be like that wise old bird

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hiding #Value! (2003 SP2)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> I like that poem...I will tell it to my son...he speaks a LOT!!

    TX for the compliment on my avatar. Though sparrows are crowding my backyard nowdays, my main reason for giong with an owl is that it has more character with those big eyes and all! So an owl it was.

    Cheers
    Regards,
    Rudi

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hiding #Value! (2003 SP2)

    I don't completely understand how you have the cond formatting setup. If setup correctly it should "hide" the cells with the errors and not require the values in other cells.

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding #Value! (2003 SP2)

    Hi Steve

    Each of the cells with the error gets its results from another page, for example =sheet1!F45, F45 on Sheet 1 is itself populated by a result from other cells, i.e. =SUM(F5:F44), these are hidden cells. By taking Rudi's advise and removing the $ sign =ISERROR(D10:F29) it now works OK.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Hiding #Value! (2003 SP2)

    It should just be <code>=ISERROR(D10)</code> since ISERROR(D10:F29) will return an array of TRUE/FALSE values and the conditional formatting will only look at the first one anyway.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding #Value! (2003 SP2)

    Thanks Rory

    I am a little puzzled, does that mean that I have to individually go into each of the cells between D10 and F29 and set the Conditional Formatting seperately?, by following Rudi's advice all of the #Value!'s have become hidden and the cells are being populated one the other sheets receive data

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hiding #Value! (2003 SP2)

    If you select the range D10:F29, D10 will normally be the active cell within the selection.
    If you then select Format | Conditional Formatting and specify the formula =ISERROR(D10), Excel will adjust this formula automatically for the other cells within the selection, just like when you copy/paste a formula. This is because the cell reference D10 is relative. If you had used the absolute reference $D$10, Excel would *not* have adjusted it for the other cells.

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding #Value! (2003 SP2)

    Thanks Hans

    That explains it better for me

    Love the new strapline

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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