Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP - 'Named' functions
I have a spreadsheet (Excel 2000) that I use to calculate/check costs using some lengthy (complex?) conditional formulas. These formulas have been developing over a period and have become too unwieldy to leave lying around in a worksheet (the sheet is used by others as well). as a result I tried a couple of other avenues without great success until I discovered named functions (thanks again Mr Walkenbach!!). These work fine, except... I build the sheet from code, and when I add the function I'm getting mixed results - either the formula is truncated, or I get an error value... I worked out that there is apparently a string length limitation (267 characters?) and have split the formula and replaced cell references with named ranges where possible to keep the length down, but this doesn't seem to make a difference. the code I'm using is as follows (apologies for the wrapping..) ActiveSheet.Names.Add Name:="Dur", RefersTo:="=IF(OR($A1="""", _ $F1=""Call Connect"",$G1=""Sensis 1234""),"""",(LEFT($I1, LEN($I1)-3) _ * 60) + RIGHT($I1, 2))" ActiveSheet.Names.Add Name:="Cost", RefersTo:="=IF(OR('Long _ Dist'!$A1="""",'Long Dist'!$L1="""",Rate=0),"""",IF((Rate/60)*'Long _ Dist'!$L1<=Min,(Min+FFall)*1.1,IF('Long Dist'!$L1CapPer, _ (((Rate/60)*('Long Dist'!$L1-CapPer))+FFall+CapVal)*1.1,costA)))" ActiveSheet.Names.Add Name:="CostA", RefersTo:="=IF(AND('Long _ Dist'!$L1<=CapPer,(Rate/60)*'Long Dist'!$L1CapVal),(CapVal + _ FFall)*1.1,IF(OR(AND((Rate/60)*'Long Dist'!$L1 Min,'Long _ Dist'!$L1<=CapPer,(Rate/60)*'Long Dist'!$L1<=CapVal), _ AND(Min=0,CapPer=0)), (((Rate/60)*'Long Dist'!$L1) + FFall)*1.1,""""))" The first (shorter) one works fine, yet the others repeatedly fail. I've been pulling my hair out about this (and I really can't afford to lose any more!!) FWIW, the formula works fine when written to a cell many thanks (in advance) S -- there are 10 types of people in the world, those who understand binary, and those who don't |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP - 'Named' functions
apologies to all, I reposted this in error (see 'Named' formulas help...) S |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To Stop Excel Copying Named Functions When A Worksheet Is Copied? | Excel Worksheet Functions | |||
Use OFFSET and COUNT functions within Named Ranges | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Help with 'named' functions | Excel Worksheet Functions | |||
using relative named ranges directly in functions | Excel Programming |