none
Formulas are updated after a query refresh RRS feed

  • Question

  • Hi folks,

    I'm experiencing an issue after performing a query refresh. I'm using Excel 2010.

    Upon data entry of various customer data, I perform a query against that data for one specific customer. In that table, I store that data so that I can execute the various formulas to determine the various performance measures we track. 

    Attached is the design of the sheet that contains the formulas. As you can see it is in order etc. (this goes spans down to 200 rows in order). 

    When I perform the refresh, some of the formulas are updated - like some rows have been removed, without removing the rows. This is the before query state

    What occurs upon data refresh

    So instead of a continue sequential reference flow, the formula is updated and skips by the number of new records from the query. This always occurs at the last known result.

    At the moment, I manually update the sheet to include the references that have been updated.

    How do I go about preventing the update obliterating my formulas?

    Many thanks,

    P.S. Attachments can't be inserted as I've not been verified I've inserted a drop box link below with the intended post.

    - https://www.dropbox.com/sh/a8jtz30m047duma/AABvifynwM4HwuZGKCxvGL3wa?dl=0


    • Edited by Job-No Tuesday, November 5, 2019 5:36 AM
    Tuesday, November 5, 2019 5:20 AM

All replies

  • Hi,

    What kind of data source did you use to create the Query?

    How did you create the connection?

    As you said you filter the specific user first, where did you filter the data in data source or in Excel?

    It's not very clearly about this problem, please be a bit more precise to explain your problem so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Tuesday, November 5, 2019 8:30 AM
    Moderator
  • Hi Emi,

    Thanks for the engagement.

    I created the data source from within Excel - same spreadsheet file. The data is read from the master data sheet, where the results are updated into another sheet (relevant to the customer). I have another sheet that has the if statement regarding the results - display (where I'm experiencing the issue), otherwise leave blank along with other calculations that are performed within that sheet regarding the customer.

    Thanks,

    Tuesday, November 5, 2019 9:11 AM
  • I've placed a copy of the spreadsheet within the dropbox link.
    Wednesday, November 6, 2019 1:58 AM