Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula errors in converted Excel spreadsheet (Excel 2000)

    Me again (sorry!). I am working with a spreadsheet that was originally a Lotus 123 file and I Saved As an Excel file (see attached). For some reason the formula in column F works fine until about halfway down when I start getting #VALUE! Typically that represents an error in your formula, but since this is the same exact formula as each of the cells above it, I'm having difficulty figuring out what the problem is. I thought that perhaps there may be some inherent problem converting Lotus files over, but if that were the case, why would the first half of the spreadsheet work. I also tried starting at the last "good" formula in column F and AutoFilling down - in hopes of copying and pasting "good" over whatever has gone "bad" - but no luck. Any ideas? Thanks.

    p.s. This is happening on several of the spreadsheets converted from Lotus.
    p.p.s. No, this is not MY check register, thank god! (All the negative numbers! Eeek!)

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula errors in converted Excel spreadsheet

    For some reason you had a "Null" or blank value in E27 and E28. If you delete the contents of those cells, you'll find everything clears up.

    As a general maintenance issue going forward, you might like to copy and paste the converted sheets to new workbooks as corruption may creep into them over time.
    Gre

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula errors in converted Excel spreadsheet

    Fantastic! How did you diagnose that? How did you see a NULL (blank) value in those particular cells, seeing as how so many other cells appear "blank" (i.e. there's no value in them) too.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula errors in converted Excel spreadsheet

    <img src=/S/invisible.gif border=0 alt=invisible width=15 height=15>Intuition! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Given what you had said, I was looking for corruption on data conversion. It was the first thing I tried. Sometimes, you have to rekey whole sets of values - which can get quite tedious.

    If you do decide to migrate the data to new workbooks, you will need to migrate the print macro as well - if you reckon it'll ever be needed again.
    Gre

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula errors in converted Excel spreadsheet

    The print macro? I wasn't even aware that there was a "print macro"

    What is the best way to copy and paste to get rid of corruption? I know that in Word, you can Paste Special without and formatting and this will usually get rid of corruption...but when you attempt to Paste Special in Excel, you get more options that a Chinese TakeOut restaurant. I'm also looking for the simplest solution as the person who these spreadsheets belong to is not THE most computer literate person, so an easy WHY and HOW are important to him.

    I appreciate your help, and your intuition.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formula errors in converted Excel spreadsheet

    For the specific issue of unwanted null values, selecting the column of data (only one at a time), then selecting Data | Text to Columns | Fixed Width | Finish, often cleans the data up.
    -John ... I float in liquid gardens
    UTC -7DS

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula errors in converted Excel spreadsheet

    Tried that and it worked. Now...if you could explain WHY that worked. That was the most bizarre solution ever.

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula errors in converted Excel spreadsheet

    For converting the files, you'll find that everything - other than Cells F3 to F55 is a value. One way of dealing with the data is to highlight the entire data area and use Paste Special|Values. Then copy again - but only F3 to F55 - and choose Paste Special|Formulas. Finally, copy the entire area and use Paste Special|Formats.

    For migrating the print macro, choose the keys Alt and F11 simultaneously and you will see the VBEditor. In the top left hand corner of the Editor, there should be a small window a bit like Windows Explorer. This is the Project Explorer. There should be itmes there for each of the files you have open in Excel. Inside the brackets at the end of each name witll be the Excel File name. Before the brakcets may be something as simple as VBAProject. Click on the old file and a tree should expand. At the bottom of this will be a folder called Modules. Inside that folder will be an icon named Module1. Drag that Module1 icon inside the Project Explorer until your cursor is over the new file and then let go. You should then find that there is a Modules folder in the tree of the new file and that Module1 has been copied into it. Once you have finished, you can close the VBEditor.

    Are you saying that you do not get a macro warning when you open the file that you posted? If you do not, as the Macro is not signed, your Macro Security is probably set too low and you would be vulnerable to malicious code launched in Excel. Check Tools|Macros|Security in Excel and make sure that it is set to at least Medium, if not High. For general safety, you also need to check your settings in Word and Outlook - as well as Powerpoint, Access, Project and Visio - although you may not be a user of these latter applications.
    Gre

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formula errors in converted Excel spreadsheet

    I learned that solution here from other Loungers. I don't know the technical details, but I assume that when the routine in Excel to parse text and numbers is run, it discards orphaned spaces, nullstrings, and other characters it doesn't regard as either valid text or values.
    -John ... I float in liquid gardens
    UTC -7DS

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula errors in converted Excel spreadsheet

    The most important thing that happens on Data,text to columns is that Excel re-evaluates the entries and interprets them to ensure they are used as intended: data as data, dates as dates, text as text, etcetera. In fact it is the same process Excel follows when one enters something into a cell manually, where it tries to figure out what you have entered.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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