Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a many to one workbook/spreadsheet situation. I have many workbooks that point to a single workbook A (master workbook). Each external worksheet has a unique cell that it references in workbook A column G.

Master workbook A is in alphabetical order and column G is updated frequently. From time to time, I have to add or delete a row of data in the master worksheet. When I insert or delete a row in this master worksheet... it throws off all my cell references in the external sheets.

How can I insert or delete a row in my alphabetized master worksheet A and have Excel update the cell references in the external worksheets to correspond with their new row location?

Hope that makes sense and thanks in advance for any help.

share|improve this question

This may not be practical in your situation, but if all the workbooks that reference your master workbook are open when the master has a row inserted, then their references to the master workbook will be adjusted automatically when that insert occurs.

share|improve this answer
Thanks so much for your reply. You are correct in that it is not practical to have all of them open. There are almost 200 worksheets.Perhaps I need to rethink how this is all setup. Any thoughts or suggestions would be greatly appreciated. – meedison Apr 30 '12 at 11:01
Its a little hard to offer any advice without more details of how your sheets work. That said, it many be worth considering named ranges on the master sheet, of some sort of synchronisation macro. – chris neilsen Apr 30 '12 at 12:45
@meedison If the situation is so complex and large, you are somewhat exceeding the scope of Excel. You should consider having a program written for your situation using a database instead of spreadsheets in my opinion. – Jeff F. Apr 30 '12 at 16:31
I have thought that I as well. I may very well be exceeding the scope of Excel. Thanks for your suggestion. I will definitely consider this. – meedison May 1 '12 at 2:05

Option 1: Skip the requirement that column G must be alphabetized. Delete rows by marking them as deleted and always insert rows at the end of the sheet. This of course to keep the external references stable.

Option 2: Change the external references to a key value (unique identifier for a specific row) instead of a specific cell and use VLOOKUP or similar to retreive values from column G.

share|improve this answer
I will give this a try and post the outcome. Thanks for your help! – meedison May 1 '12 at 2:02

if you use excel 2007+, i recommend using Data > From Other Sources > From Microsoft Query:

  1. choose Excel File and select your master excel
  2. choose columns (if you don't see any list of columns, make sure to check Options > System Tables)
  3. if needed, edit the Data > Connections > choose your new connection > Properties > Definition > Command text like SQL...
share|improve this answer

Here's how without having both worksheets open...

In the original worksheet, go to the cell that you will refer to externally. Right click and select "Define Name." Type in a name for that cell, i.e. "Sales2012" then select "Okay"

In the worksheet that will refer to that cell, change the external reference from =[source.xls]SheetName!g26 to =[source.xls]SheetName!Sales2012

Now you can add or delete rows or columns as much as you want, without having all files open, and they will update. Cheers!

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .