Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Link Problem (2007)

    For my company I have created a Master forecasting spreadsheet that is made up of many of our department's forecast spreadsheets. Each departments spreadsheet is its own file. To create my Master forecast I copied the cell from each departments forecast that contained the sum total for the department and pasted it into the Master sheet in the appropriate cell for that department. I also linked the data and set the properties so that the values would be updated automatically whenever the Master sheet is opened.

    The problem I am running into is that whenever the department heads change the row that the total is in by adding or deleting a row, it screws up my link - the data gets updated, but not with the correct cell or total. I need to allow the department heads to add rows to their forecasts but, I would like to somehow "lock" the total cell in my Master sheet. How do I do this? Did I go about this all wrong?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Link Problem (2007)

    Welcome to Woody's Lounge!

    You can use defined names for this:
    - Select the cell with the total in a department worksheet.
    - Click in the address box on the left hand side of the formula bar.
    - Type a name, e.g. Total.
    - Switch to the master worksheet.
    - Change the formula that refers to this department's total from something like

    =[Accounting.xls]Sheet1!$G$10

    to

    =[Accounting.xls]Sheet1!Total

    The defined name will keep on referring to the cell with the total even if rows are inserted or deleted.

  3. #3
    New Lounger
    Join Date
    May 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Link Problem (2007)

    It worked great, thank you!

Posting Permissions

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