master type objects in excel

[kennystringer]

Hello,
I would like to know if excel has the capacity to build a master copy of say a table that can be copied into numerous excel worksheets where all the copies can be edited at once just by editing the master copy.

Help appreciated,
Kenny

[BobBridges]

Well, you do have the capability of having a formula point to a cell in another workbook. For example, suppose you're doing a MATCH:

=MATCH(RC1,C3,0)

That takes the value in the first column of the current row, and looks it up in column 3, returning the row where the value was found, or #N/A if it wasn't there. Or if the table exists in a separate worksheet (as it usually does):

=MATCH(RC1,OtherSheet!C3,0)

Now, if you want to do the lookup in a table that's in a whole other workbook, the format is modified like this:

=MATCH(RC1,[Otherworkbook.xlsx]OtherWorksheet!C3,0)

The best thing to do is to open that other workbook, set up the Match or the VLookup or whatever you're doing by pointing to the other table, and then see for yourself how Excel said it in the formula. That's because Excel will remember to get the syntax right, including quotes and whatever else I may have done improperly in the above example.

So the first part of your question is that you don't even have to copy the master table to the current workbook; you can just do the lookup directly.

The second part is that you can, I suppose—if you want to—copy the master table, by just filling up a worksheet in your current workbook to say something like

=[OtherWorkbook]Otherworksheet!RC

Then your lookup can point to a worksheet here in the local workbook. But why would you want to?

And I have yet a third answer, or more a sort of side issue: You should beware of this, because in my experience lookups in external workbooks take a lot longer, especially if you don't open them first. Better, maybe, to have a macro go fetch the master and copy it into a new worksheet of the current workbook, at least if the table is very sizable. That's what I do, mostly, FWIW.