Formula File Reference too long (Excel 2003)

[cw2]

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.

[HansV]

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

[cw2]

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)))))

[HansV]

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]

[Andrew Cronnolly]

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

[sdckapr]

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

[cw2]

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.

[macropod]

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