0

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.

Improve this question

5 Answers 5

Reset to default
3

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.

=VLOOKUP("uniqueId";[WorkbookA]Sheet1!A1:G1000;7;FALSE)
Improve this answer
1
  • I will give this a try and post the outcome. Thanks for your help!
    – meedison
    Commented May 1, 2012 at 2:02
2

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.

Improve this answer
4
  • 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
    Commented Apr 30, 2012 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
    Commented Apr 30, 2012 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.
    Commented Apr 30, 2012 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
    Commented May 1, 2012 at 2:05
0

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...
Improve this answer
0

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!

Improve this answer
0

Had the same problem simple solution after browsing the internet for some time

ctrl select multiple tab while being on the tab where you have your reference

Then right click where you want to have your row added

Then select insert in the drop down

Improve this answer

You must log in to answer this question.

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