Changing multiple, linked Excel Workbooks

[PTW]

Hi there,

I've inherited a series of Excel Workbooks:

1. Outside users send in updated Excel Workbooks which get saved into a directory.
2. A "master" workbook has hard-coded links to all of these workbooks and updates a worksheet. For example, B4=wkbk1!Sheet1!E1 + wkbk2!Sheet2!E1.
3. Other workbooks in turn have links to the "master" workbook.

Here's the problem: these workbooks contain Sales Rep info, and the Reps come and go. So we end up with "holes" in the ranges of data or else the Reps get mixed up (ideally they would be grouped together on the worksheets by location, but when a new Rep is added, they have to go to the end of every sheet to avoid mixing up the formulas).

Is there any way I can make these links work dynamically so that if I have to add a Sales Rep into the middle of a list it won't break all of the other links? The way it is setup right now, I cannot simply open up all of the workbooks in question because that throws off the formula references. I've heard that Named Ranges might be the answer, but from what little I've seen so far those always seem to operate at the level of the entire range. So I can do things like SUM(myrange), but I might not be able to pick out the 3rd cell in that range.

I'd appreciate any advice!

 

[Hasit]

If you set up a series of cells with a range name, you can still explicitly reference any cell in that range.

For example, if A1 through A3 cells are called "MYCELLS", I can either reference "MYCELLS" and test for a condition (for example), or I can test for the same condition but only in cell A2.

Now, in order to group cells together so that there are no holes, you would either need to do some fancy VBA, whilst maintaining the links etc, or create a set of formulas to remove the blanks, whilst maintaining links etc.

I think you are right, range names will probably go a long way to resolving your issue.

 

[iGrant]

To pick a cell from a range, you can use the Index function

=INDEX(NamedRange,2,1)

will refer to the cell in the 2nd row of the 1st column of the named range.

Any use?

To address the issue of your linked sheets, I'd be tempted to have the sheets structured with a column of data with the salesperson's name, region, etc and then use SUMIF (or probably {SUM(IF... ) to extract the summary data. Would need to know more about the structure of the spreadsheets to be able to help, however.

 

[PTW]

Ah..."Index" is one thing I was looking for. Thanks!

For now I'm just using a whole bunch of VLOOKUP's. That gives me some flexibility in terms of adding/deleting/moving info around, but is not perfect. Now that I know how to get a particular cell from a range, I'll have to think about whether a range is more useful or not.