'Named' formulas problem
 

i have a spreadsheet that uses some lengthy (complex??) conditional formulas
to check some calculations (using Excel 2000).

Over a period I have tried several different ways of implementing this and
am currently trying to use a 'named' function (thanks Mr Walkenbach...)

problem I have is that the sheet is built on the fly (from code) and when
I try and add the name/formula, I'm getting (very) mixed results. Shorter
examples work fine, but I have several formulas that are quite long, and
when I add them, I formulas get truncated or I get an error value...

I worked out that there appears to be a length limitation (267 characters??),
and have split the formulas to reduce the length and replaced cell
references with named ranges where possible, but it's not helping...

code used 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,""""))"

First one works fine, but the subsequent 2 are the ones giving me trouble

pasting the formulas back into a cell gives the desired result, by the way...

If anyone has any ideas, I'd surely appreciate your assistance

S

--
there are only 10 types of people in the world,
thos who understand binary, and those who don't...

'Named' formulas problem
 

I have not done this but it might work:

Break your long formula into logical components then Name each component.
Then in your vba code re-assemble it something like:

=If(Component1,Component2,Component3)

"Simon" wrote:


i have a spreadsheet that uses some lengthy (complex??) conditional formulas
to check some calculations (using Excel 2000).

Over a period I have tried several different ways of implementing this and
am currently trying to use a 'named' function (thanks Mr Walkenbach...)

problem I have is that the sheet is built on the fly (from code) and when
I try and add the name/formula, I'm getting (very) mixed results. Shorter
examples work fine, but I have several formulas that are quite long, and
when I add them, I formulas get truncated or I get an error value...

I worked out that there appears to be a length limitation (267 characters??),
and have split the formulas to reduce the length and replaced cell
references with named ranges where possible, but it's not helping...

code used 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,""""))"

First one works fine, but the subsequent 2 are the ones giving me trouble

pasting the formulas back into a cell gives the desired result, by the way...

If anyone has any ideas, I'd surely appreciate your assistance

S

--
there are only 10 types of people in the world,
thos who understand binary, and those who don't...