Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2003
    Location
    Bristol, Avon, England
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula File Reference too long (Excel 2003)

    I have a formula which contains a file path. Trouble is we have a very long and immovable file structure. As a result the formula displays an error message saying the "formula is too long" - the file path cannot be shortened. I am sure there is a simple work around.

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

    Re: Formula File Reference too long (Excel 2003)

    Can you assign a drive letter to the folder containing the file? That would shorten the path considerably.

  3. #3
    New Lounger
    Join Date
    Dec 2003
    Location
    Bristol, Avon, England
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula File Reference too long (Excel 2003)

    True - I tried that already, we are talking stupidly long file paths - here is an example of a typical formula with the file path:

    =IF(C13="quarter 1 2005/2006" VLOOKUP($C$14,'G:Crime & DrugsAdminister Public FundingManage funding expenditureMonitor funding budgetsBudget utilisation infob.Execution2005_2006[Funding Grants Payment Control Sheet 05_06.xls]BSC+ASB Allocations'!$A$2:$Y$36,12,FALSE),IF(C13="Quarter 2 2005/2006" VLOOKUP($C$14,'G:Crime & DrugsAdminister Public FundingManage funding expenditureMonitor funding budgetsBudget utilisation infob.Execution2005_2006[Funding Grants Payment Control Sheet 05_06.xls]BSC+ASB Allocations'!$A$2:$Y$36,15,FALSE),IF(C13="quarter 3 2005/2006" VLOOKUP($C$14,'G:Crime & DrugsAdminister Public FundingManage funding expenditureMonitor funding budgetsBudget utilisation infob.Execution2005_2006[Funding Grants Payment Control Sheet 05_06.xls]BSC+ASB Allocations'!$A$2:$Y$36,18,FALSE),IF(C13="quarter 4 2005/2006" $C$14 G:Crime & DrugsAdminister Public FundingManage funding expenditureMonitor funding budgetsBudget utilisation infob.Execution2005_2006[Funding Grants Payment Control Sheet 05_06.xls]BSC+ASB Allocations'!$A$2:$Y$36,21,FALSE,0)))))

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

    Re: Formula File Reference too long (Excel 2003)

    Start Windows Explorer.
    Select Tools | Map Network Drive...
    Browse to the folder corresponding to 'G:Crime & DrugsAdminister Public FundingManage funding expenditureMonitor funding budgetsBudget utilisation infob.Execution2005_2006.
    Assign it to, say, Z: and make sure that 'Reconnect at logon' is ticked.
    You can then use

    Z:[Funding Grants Payment Control Sheet 05_06.xls]

    instead of

    G:Crime & DrugsAdminister Public FundingManage funding expenditureMonitor funding budgetsBudget utilisation infob.Execution2005_2006[Funding Grants Payment Control Sheet 05_06.xls]

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula File Reference too long (Excel 2003)

    An alternative to Hans's suggestion would be to define a Name (e.g Path) in the workbook with formula, which referes to

    'G:Crime & DrugsAdminister Public FundingManage funding expenditureMonitor funding budgetsBudget utilisation infob.Execution2005_2006[Funding Grants Payment Control Sheet 05_06.xls]BSC+ASB Allocations'!$A$2:$Y$36

    You could then replace all instances of the above external range with Path.

    Andrew C

  6. #6
    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: Formula File Reference too long (Excel 2003)

    Since they all seem to have the same lookup, just different columns, why not shorten it and use the path only once and have just the new column in the IFs (warning air code, watch for typos):

    =VLOOKUP($C$14,'G:Crime & DrugsAdminister Public FundingManage funding expenditureMonitor funding budgetsBudget utilisation infob.Execution2005_2006[Funding Grants Payment Control Sheet 05_06.xls]BSC+ASB Allocations'!$A$2:$Y$36,IF(C13="quarter 1 2005/2006", 12,IF(C13="Quarter 2 2005/2006, 15, IF(C13="quarter 3 2005/2006",18,IF(C13="quarter 4 2005/2006" 21)))),FALSE)

    It could be shortened even more by seeting up a VLOOKUP to lookup the col based on the value in C13 (would require an intermediate table of value and column)

    Steve

  7. #7
    New Lounger
    Join Date
    Dec 2003
    Location
    Bristol, Avon, England
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula File Reference too long (Excel 2003)

    Thanks all three of you who replied - the lookup reference worked best for our particular needs but worth knowing about the other two solution too.

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula File Reference too long (Excel 2003)

    You'll also find that opening the source file will allow Excel to show its name in the formula without the path. That will then allow you to edit the formula without the formula length limitation being so much of an issue. Closing the file after editing the formula doesn't appear to have any ill effects, even though the formula then includes the path and may be much longer than the 1024 character limit.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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