+ Reply to Thread
Results 1 to 6 of 6

Zeros in cells

  1. #1
    Registered User
    Join Date
    10-13-2007
    Location
    NW England, Lancashire
    MS-Off Ver
    Office for Mac 2011
    Posts
    42

    Zeros in cells

    Apologies for the previous incorrect titling

    Hi all, I have really 2 questions to ask please.
    Firstly, I have 3 sheets. Sheet 2 must copy certain data on sheet 1 when inputting to sheet 1. I have extended the formula in sheet 2 but it is displaying a series of 0's where a cell on sheet 1 is empty. I have managed to get sheet 2 to refer to completed cells on sheet 1, but not if the cell is empty. Basically i do not want the zeros.

    Secondly, i want to enter certain data on sheet 3, if the same data is on sheet 2 then i would like the row containg that data highlighting.

    Any help or tips would be appreciated, Thanks

    Mike

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    For part 1

    Try > tools > options > view and untick zero values


    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    10-13-2007
    Location
    NW England, Lancashire
    MS-Off Ver
    Office for Mac 2011
    Posts
    42
    Thank you for that, I didn't even know that section existed. Now i am left with a small green triangle in the top left corner of each cell, i will try to clear them now. Thanks again.

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    You can also use conditional formatting

    http://www.contextures.com/xlCondFormat01.html

    Were value equal zero

    or

    Format the cells as
    0;;
    or

    Use a if statement
    =IF(Sheet1!A1="","",Sheet1!A1)
    VBA Noob

  5. #5
    Registered User
    Join Date
    10-13-2007
    Location
    NW England, Lancashire
    MS-Off Ver
    Office for Mac 2011
    Posts
    42
    Many thanks again for your input, unfortunately i am not that far along the road to fully understand your instructions. I will have to keep trying, i do understand the conditinal formatting stuff but i dont know the symbols to use to make it do what i want, insofar as when i enter some data in sheet 3, regardless of its position on sheet 2 i want it to highlight the full line and not just the corresponding cell.
    Thanks

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Locked for cross posting

    Amended
    Now Opened

    VBA Noob
    Last edited by VBA Noob; 10-14-2007 at 02:12 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1