Results 1 to 9 of 9
  1. #1
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Path/File variables in link formulas? (Any)

    Hi all,

    Haven't found an answer for the following; has anyone dealt with something this one before:

    Scenario: Sales reporting and management application; separate workbooks for Sales Manager and Sales Reps (up to 20 per manager). Sales reps send their workbooks monthly to sales manager, who puts them into working folder with their sales manager workbook.

    Sales rep data then flows automatically into sales manager workbook via linking formulas such as:

    ='C:PipelineRepA.xls'!Rep_SMonthTotal*RepAConvFact or
    and
    ='C:PipelineRepB.xls'!Rep_SMonthTotal*RepBConvFact or
    etc.

    C:Pipeline is the working folder on my dev machine, but it can be any path/folder at all on the users' machines; Excel automatically updates the path/folder info on the users' machines.

    All fine so far. However, I also need to have some similar linking formulas in each of the RepA through RepT workbooks, that read back to values entered in the Sales Manager workbook. That is because the Sales Manager enters some assigned values for each Rep - such as Sales Quota, Region, Office, Currency etc. - in the Sales Manager's workbook. These values then get updated into each of the Rep A through Rep T workbooks that are residing in the Sales Manager's working folder, and then sent back out to the Sales Reps. The opening and updating of the Rep A through Rep T workbooks can be done via a macro in the Sales Manager's workbook.

    This also all works fine, but the problem I'm hitting is that each of the 20 Rep workbooks, each has about 10 formulas that refer back to the Sales Manager's workbook - to bring in the values which the sales manager has entered for that rep. These formulas look like:

    ='C:PipelinePipelineManager.xls'!RepACurrency
    and
    ='C:PipelinePipelineManager.xls'!RepBCurrency
    etc.

    The problem is that I do all the development/maintenance for the Rep workbooks in RepA.xls, and then copy out 19 more copies and rename, each time I need to distribute an update/revision of the Rep workbooks. The issue is, how do I avoid having to go in and manually change all 10 references in all 20 workbooks, from RepA to RepB, etc. (since every time I do an update, they are copied from the RepA workbook.) I could open each workbook and run a little replace macro, but still not too convenient. I could also probably have one central macro that opens up each Rep workbook in turn, and replaces the letters in the formulas, but would rather do the following, if it is possible to do:

    Ideally I'd like to put the "RepA" or "RepB" value into one named range in each rep's workbook, and have the linking formulas in that workbook somehow pick up that variable value. But nothing I've found so far allows me to introduce any kind of variable into that linking formula: Excel wants to take that formula string literally.

    Has anyone encountered this requirement, and is there a clever way to achieve this?

    Thanks,
    Gary

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

    Re: Path/File variables in link formulas? (Any)

    You can use the INDIRECT function. Let's say that Sheet1!A1 contains the text RepA (or RepB etc.).
    The formula
    <code>
    =INDIRECT("'PipelineManager.xls'!"&Sheet1!A1&"Curr ency")
    </code>
    will do what you want. If you give the range containing the text RepA (or RepB etc.) a name such as RepName, you can use
    <code>
    =INDIRECT("'PipelineManager.xls'!"&RepName&"Curren cy")
    </code>
    Note: PipelineManager.xls must be open for INDIRECT to work.

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Path/File variables in link formulas? (Any)

    Hans,

    INDIRECT will work but as you say, only if PipelineManager.xls is open.

    In the current situation, PipelineManager.xls won't always be available:

    PipelineManager.xls can be available and open while the sales manager is updating the values into each of the individual rep workbooks.

    But at that point, the individual rep workbooks get sent back out to the individual reps, who won't have access to the PipelineManager workbook. As it is, we tell the reps to select "Don't Update" when opening their workbooks, so I was curious to test whether INDIRECT might not give a #REF error, if the individual reps open their workbooks and select "Don't Update". But even if they choose "Don't Update", the INDIRECT functions give #REF errors anyway.

    So I guess my fallback will be to write a macro which the sales manager can run from their workbook, which will cycle through all the rep workbooks while they're in the sales manager's working folder, and update the rep names in the relevant formulas in each rep workbook - was just hoping to find some really easy way to avoid that!

    Thanks though for the suggestion.

    Gary

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Path/File variables in link formulas? (Any)

    Laurent Longre's has a <img src=/S/free.gif border=0 alt=free width=30 height=15> add-in called MoreFunc (available from Excel add-ins) that contains a function (indirect.EXT) that does work with references to closed workbooks.

    All your users would have to install the addin in XL and it would not require the file to be open.

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Path/File variables in link formulas? (Any)

    If you open all files (sources and targets) in one session of Excel and save-as the "source" files to a new name, all links are automatically reworked to the new file names.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Path/File variables in link formulas? (Any)

    Thanks Steve (and Jan) for the suggestion. The add-in is impressive and I'll have to play around with it.

    The users are at a client of a client, in offices worldwide, so I've probably got little chance of getting that add-in installed.

    I'll just bite the bullet and write the macro. I erred in my reply to Hans in saying that the sales managers will need to run the macro - really I'm the only one who will need to run the macro, whenever I need to hive off a batch of updated rep workbooks for redistribution to the sales managers due to some revision or fix - so the cleanest thing is just for me to do the work and write the macro for myself.

    Which touches on another issue - because this application is mainly formula-driven rather than macro-driven, there's no effective way to distribute fixes or changes to the workbook logic, separate from the workbook data (or is there?). If the application were run by macros, I could house the code in an .xla, so that any changes to the logic could be distributed without needing to touch the data that's already in the users' workbooks.

    In an application that's driven by formulas, distributing changes to the logic appears to be impossible to do, without distributing new workbooks - but then you get into needing to migrate the data that's already in existing workbooks (scattered all over an organization) into the new workbooks.

    This is much easier to deal with in Word (with which I'm more familiar) since everything tends to be driven from templates and add-ins, and there's no real parallel to Excel's "logic in the workbook". How do you as Excel developers deal with this issue? (other than testing like crazy to minimize the need to distribute a revision or fix!)

    Thanks again,
    Gary

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Path/File variables in link formulas? (Any)

    Well, since you already have external references anyway, separating data from logic should be a snap, provided that you keep all data in external file(s). That way, you just send them the logic file again and off you go <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Path/File variables in link formulas? (Any)

    Thanks, Jan.

    This probably should become the topic of another thread, but in brief:
    Both of the Excel apps I'm currently working on were inherited from previous developers, and in both cases, the data is mixed with logic in the same workbook. In both cases, the original developer was someone who had an in-depth understanding of their organization's business logic, and knew just enough about formulas and macros in Excel to build something that was quite useful for their department or organization. But at the same time, no thought was given to application architecture issues such as separating data from logic.

    Based on that admittedly small sampling, I got the impression that Excel apps of this kind are very commonly encountered - Excel makes it so easy for someone with business expertise to build a useful application with worrying about architecture.

    In both cases, the complexity of the applications eventually outgrew the ability of the original developers to maintain them, and at that point, someone with more VBA expertise - me - was brought in to take them over. I'm getting the impression that this is a very common scenario as well.

    What I'm only just learning to focus on is that in these cases, there are usually architectural problems, and it's a whole lot harder to implement a sensible architecture, once the application is already in widespread use and there are dozens or hundreds of users out there with workbooks full of logic and data. In some cases there may be elegant ways to get the architecture upgraded and the data migrated, but in other cases not. Sounds like fun!

    Gary

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Path/File variables in link formulas? (Any)

    If this is a widely used thing, I'd say there's even more motivation to separate data and logic. Of course this will cause some pain with older models in the beginning. But the benefits will outweigh that in future I'm sure.

    It shouldn't be very hard to build an "Import old model" utility. You can offer that as a separate sevice (and for a reasonable price of course <g>).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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