Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filling fields (Office 2003)

    Good afternoon

    Melanie B posted problem whereby she wanted to select a name from a Combo box and then other fields would automatically be completed, i.e. from Combo box select Customer A and cell A2 = Address 1, B2 = Address 2 etc. HansV kindly replied with the following:
    .................................................. .................................................. .................................................. .................................................. .............................................

    Your lookup table is not sorted on column A, and you want exact matches, not approximate ones. Therefore you must add FALSE as 4th argument to all VLOOKUP formulas (FALSE = do not approximate), for example

    =VLOOKUP($A$2,Tables!$A$2:$E$8,2,FALSE)
    .................................................. .................................................. .................................................. .................................................. ..............................................
    which I have adapted to suit my needs, however I have 2 fields that may or may not contain information, if there is no information Excel is by default filling the field with a 0, is there any way that I can stop it doing that.

    Thank you

    Danny

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

    Re: filling fields (Office 2003)

    If the data are numeric, and if 0 doesn't occur otherwise, you can set the number format to suppress zero values, for example a custom format 0;-0;

    You can also let the formula suppress zero values:

    =IF(VLOOKUP(...)=0,"",VLOOKUP(...))

  3. #3
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filling fields (Office 2003)

    Thanks for the quick response, I will try to use your suggestion

    Cheers

    Steve

  4. #4
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filling fields (Office 2003)

    Thanks Hans that worked fine, whilst playing around and trying to learn more I noticed that if you go to 'Tools', 'Options' there is a check box for 'Zero Values', if I check this it removes all zero value from my sheet. Can you tell me what the advantages / disadvantages may be in using this?

    Cheers

    Danny

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

    Re: filling fields (Office 2003)

    If you clear the 'Zero values' check box in the View tab of Tools | Options..., Excel will not display any zero value in the current window, whether it is typed directly by the user or the result of a formula. In many situations, it is not desirable that zeros typed in by the user are hidden. So while clearing 'Zero values' can be useful, you should only apply it in situations where it doesn't matter if directly entered zeros disappear. Otherwise, it's better to suppress the display of zero values for specific cells only, by using a custom number format or by using formulas such as the one I suggested.

Posting Permissions

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