Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Blank data fields (2000)

    Hello,

    I have multiple sheets in one workbook. The first sheet imports the data from Access.

    The second sheet does calculations based upon the data from the first sheet.

    We are using this workbook to analyze data. We are constantly importing data from Access, looking at it, clearing it and importing (different) data. It works fine unless the data has less columns than the previous data imported. The second sheet will then give the error message #REF! wherever there is blank data in the first sheet. The only way to correct this is to copy the formulas over the error and it corrects it.

    What am I doing wrong?

    Does this make sense?

    <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Blank data fields (2000)

    When you clear the old data, do you delete the columns ?. If so this will cause probelms with any formulae that reference the columns (or rows).

    If this is the case, just delete the existing data by using Edit, Clear Contents. Or just press the delete key.

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank data fields (2000)

    Thanks for your reply. I am doing exactly that - edit/clear contents. It does not work.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Blank data fields (2000)

    Could you post a sample formula that evaluates as #REF so that we can see what is happening. Does the data that is cleared contain formulas ?

    Andrew C

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

    Re: Blank data fields (2000)

    I addition to Andrew's request for a sample, can you describe exactly the steps being used to import the data from Access?
    -John ... I float in liquid gardens
    UTC -7ąDS

  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: Blank data fields (2000)

    I wonder if this is the old problem of Access importing blank fields to NullStrings in Excel, and that is why the formulas error out, though as a possible solution it doesn't exactly fit the problem described.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank data fields (2000)

    Attached is a copy of the workbook. The first sheet called "Data" is where the data is imported. To import, I go to Data/Get External Data/New Database Query...then I find my Access Database and then the table which holds the data. After this initial set up, I clear the cells in "Data" and import again.

    The second worksheet is called "Scratch". This is where the problem lies. The cells seem to lose their references/calculations and addsin #REF! in some cells always before the last cell. ???

    Thanks for looking at this.

    Thanks.

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

    Re: Blank data fields (2000)

    On the Scratch worksheet the row reference in the formula row immediately above the error rows refer to Data Row 24, and the formula in the row immediately below the error rows refers to the very next row in the Data, Row 25. I susxpect that the #REFs are occurring because rows are being deleted on the Data sheet.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Blank data fields (2000)

    Hi Julie,

    Change your formula in A11 on the Scratch worksheet to:
    =IF(OFFSET(Data!$A$1,ROW()-10,column())="","",OFFSET(Data!B$1,ROW()-10,))
    then copy this across the range A11:B43 and the #REF! errors will dissapear. This works because the OFFSET formula will be immune to the deletion of any row except row 1 and any column except column A on your Data sheet. It could even be made immune to that too, but I doubt it's necessary in this case.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank data fields (2000)

    Thank you for your response.

    It will work with column A - because there are no calculations. However, I can not copy it over into column B because it has calculations.

    Thanks.

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Blank data fields (2000)

    Hi Julie,

    That solution should work equally well for both columns A & B from the test workbook that you posted. If you have more complex formulae in Column B on the Scratch worksheet that need to take account of the changing numbers of data rows, that too should be possible. If you could post a (sanitized) workbook showing the more complex formulae, I'm sure that I and others here would be happy to have a got at modifying them to suit.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank data fields (2000)

    Julie

    In the properties of your query you have left it at the default setting of Insert/Delete for new data. It might be worthe trying the Overwrite/Clear setting instead

    HTH

    Peter

  13. #13
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank data fields (2000)

    Thank you! Thank you!! Thank you!!! to everyone who has been helping.

    By changing the properties, it works. I have imported new data with varying lengths about 6 times and so far no #REF!.

Posting Permissions

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