Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Chatham, Ontario, Canada
    Posts
    86
    Thanks
    5
    Thanked 1 Time in 1 Post

    refresh appears to delete rows of cells

    I have a workbook which has me completely baffled. This sheet had been in service for at least three years without a problem but a couple of weeks ago it started throwing up errors and now I can't even get the basic element to work. The subject machines have either Office 2010 or 365


    The following is all done in a clean spreadsheet with a new query on Office 2010.


    The book contains the following query wich returns data to Sheet2!A1. The parameter is located in Sheet1!A1
    SELECT tbl_POCost_by_MO.`Sales Order Number`, tbl_POCost_by_MO.`Purchase Order Number`, tbl_POCost_by_MO.cost, tbl_POCost_by_MO.`Supplier Name`
    FROM `C:\data\yesican4\imhs2010.accdb`.tbl_POCost_by_MO tbl_POCost_by_MO
    WHERE (tbl_POCost_by_MO.`Sales Order Number`=?)


    This query works properly and returns
    20150516-01 20138 76.56 Duct INC
    20150516-01 20139 130.3 HP Products Inc.
    20150516-01 20140 116.3 Jacob Tubing


    The first sheet contains the aforementioned parameter value, Headings in Row 2 A through C and then several rows of cells linked to the results of the query on sheet 2.


    The problem comes when I enter a value into Sheet1!A1 and refresh. The first two rows of data are deleted from Sheet1. It makes no difference if I start the data at Row 3 or Row 30. I'm left with:
    R3 Jacob Tubing 20140 116.3
    R4 0 0 0


    If I insert 2 rows into 2 and 3 and copy the formulae then it works again.
    R3 Duct INC 20138 76.56
    R4 HP Products Inc. 20139 130.3
    R5 Jacob Tubing 20140 116.3


    When I delete the parametr and refresh then I get 0's displayed in all locations except rows 4 and 5 which have #REF! errors. More curious is the fact that Row 3 still contains references to =+Sheet2!D2 while Row 6 now contains =+Sheet2!D3
    R3 0 0 0
    R4 #REF! #REF! #REF!
    R5 #REF! #REF! #REF!


    If I delete rows 4 and 5 so that the cell references are correct again, re-enter tyhe parameter vale, and refresh then I'm back to
    R3 Jacob Tubing 20140 116.3
    R4 0 0 0


    I've constructed another sheet with a similar query based on a different database and it performs the same functions flawlessly. I'm completely lost here. Can anyone help?

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,176
    Thanks
    47
    Thanked 982 Times in 912 Posts
    Are you able to post a copy of the spreadsheet for us to review?

    cheers, Paul

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Chatham, Ontario, Canada
    Posts
    86
    Thanks
    5
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Paul T View Post
    Are you able to post a copy of the spreadsheet for us to review?

    cheers, Paul
    Yes I can but It seems I will not need to. Nothing works to clarify a problem like trying to explain it to someone else. The query was inserting rather than overwriting and causing unexpected results in the lookup. Now That I've resolved this bit I can go back to the original sheet and see if that was the only issue.

Tags for this Thread

Posting Permissions

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