• Problems with data source linking

    Author
    Topic
    #474772

    Hi
    I have two seperate workbooks, one that has a range of data in several worksheets (source workbook), this is then linked to another workbook (destination workbook).

    The linking of the data had been working fine until lately, I believe I know what the problem is I’m just not sure how to fix it.

    Recently I added a number of rows to one of the spreadsheets in the data source workbook and now the references/formulas in the destination workbook are out by the number of rows I have inserted.

    Could someone advise if there is a way that when I add/remove rows/columns in the source worksheet that the destination workbook will automatically adjust to the new rows/columns.

    Thanks in advance for any help
    Cheers
    Dax

    Viewing 1 reply thread
    Author
    Replies
    • #1267227

      You can make the source cells a Named Range and refer to that Name in your formula in the destination workbook, rather than referring to specified cells.
      In general, the Named Range will adjust itself to insertions and deletions, whereas a formula in another workbook will not, as you have observed.

      • #1267238

        You can make the source cells a Named Range and refer to that Name in your formula in the destination workbook, rather than referring to specified cells.
        In general, the Named Range will adjust itself to insertions and deletions, whereas a formula in another workbook will not, as you have observed.

        Thanks Martin that’s worked exactly how I was hoping.
        Cheers
        Dax

    • #1267230

      Make sure the destination workbook is open at the time you insert the cells.

      • #1267240

        Thanks for the advice to keep the destination workbook open while changing. In the workbooks I was asking about it was very quick to change to name references, however I have a couple of other workbooks with much larger data so will use your suggestion with then as I don’t have time at present to change all the formulas to name ranges

        Cheers
        Dax

    Viewing 1 reply thread
    Reply To: Problems with data source linking

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: