Results 1 to 8 of 8

20080713, 22:22 #1
 Join Date
 Nov 2002
 Location
 London, Gtr London, England
 Posts
 496
 Thanks
 8
 Thanked 0 Times in 0 Posts
Using Names with long formulae (XP SP2)
Dear Loungers
If I have a name that hold long formula, by the time excel has added the sheet reference and made them even longer I can't ever see the whole formula Excel will show 232 characters. Usually I have learnt to copy my original formula once I am sure of it as a text field on a sheet that holds such things as this and refernece data. This means that if I want to adjust things I can easily test changes by copying the literal to an approprate cell, make any changes then copy to the name.
However we all fail in our good intentions sometimes so is there any way I can retrieve the complete formula?
Thank you....................................... Liz

20080713, 23:18 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Using Names with long formulae (XP SP2)
Method 1:
 Select Insert  Name  Define...
 Select the name in the list.
 Click at the beginning of the Refers to box, then drag slightly down.
 This will select the entire formula. Press Ctrl+C to copy it.
 Close the dialog.
 Paste the formula into a cell.
Method 2:
 Press Alt+F11 to activate the Visual Basic Editor.
 Press Ctrl+G to activate the Immediate window.
 Type the following, substituting the correct name for Test:
<code>
? ActiveWorkbook.Names("Test").RefersTo
</code>
 Press Enter.
 You can select and copy the result.

20080714, 00:33 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Using Names with long formulae (XP SP2)
Your attachment hasn't made it (yet). Could you edit your post and add it? Thanks.

20080714, 01:17 #4
 Join Date
 Nov 2002
 Location
 London, Gtr London, England
 Posts
 496
 Thanks
 8
 Thanked 0 Times in 0 Posts
Re: Using Names with long formulae (XP SP2)
Hans,
I had tried both of these but the best I get is this:
=IF('Problems & Issues'!F7="Y",IF('Problems & Issues'!I7="Y",(10'Problems & Issues'!G7)*'Problems & Issues'!H7,(10'Problems & Issues'!G7)*'Problems & Issues'!H7*0.05),0)0'Problems & Issues'!J7="Y" 'Problems & Issues'!M7="Y" 10 'Problems & Issues'!
My original formula which was this:
IF(F7="Y",IF(I7="Y",(10G7)*H7,(10G7)*H7*0.05),0)+
IF(J7="Y",IF(M7="Y",(10K7)*L7,(10K7)*L7*0.05),0)+
IF(N7="Y",IF(Q7="Y",(10O7)*P7,(10O7)*P7*0.05),0)+
IF(R7="Y",IF(U7="Y",(10S7)*T7,(10S7)*T7*0.05),0)
It has been adjusted by Excel to include the full reference. What doesn't make any sense is a) I can't get the whole formula and if you look at the string I copied from VB editor it has substituted zero for the first plus sign (look at this bit "*0.05),0)0'Problems " the last zero shou7ld be a plus sign). In spite of this the calculation itself works. I attach a slimmed down sheet with the problem. If I use my saved "text" version of the calculation (on the ref data sheet) and copy to a relevant cell it gives the same result as the CalcScore formula therefore I assume they are both working. I follow the practice you can see in the ref data sheet having been caught out by this many times before. I was recently unwell and the sheet I had started didn't have this useful bit of housekeeping. So on returning to the sheet I couldn't remember where and how things worked, and this feature of Excel was a real nuisance. I even have Jan Karel Pieterse excellent Names tool and it does the same (well almost, it actually displays a few more characters, but not all and no plus sign).
Anyway I thought it was about time to get to the bottom of this! have you any other ideas... or is it my spreadsheet technique?
thank you.................... liz

20080714, 02:25 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Using Names with long formulae (XP SP2)
I can't even reconstruct how you got the name to work  if I try to enter a long formula, it immediately becomes invalid.
Apparently Excel doesn't handle long formulas in names very well. I'd use the formula directly in the cells themselves.

20080714, 03:03 #6
 Join Date
 Nov 2002
 Location
 London, Gtr London, England
 Posts
 496
 Thanks
 8
 Thanked 0 Times in 0 Posts
Re: Using Names with long formulae (XP SP2)
Hans,
Well I have done this for years and it works! it just doen't display properly. I was using names for the following reasons a) a useful name is so much easier to understand, and I thought it saved space since the calculation itself is only save once and in a large spreadsheet this is a good efficiency  is that right?
liz

20080714, 03:06 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Using Names with long formulae (XP SP2)
Yes, using named formulas is efficient. But not being able to view or edit it is a serious handicap  it means you won't be able to maintain the workbook easily.

20080714, 04:24 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 Posts
Re: Using Names with long formulae (XP SP2)
I can enter that formula as a name if I put it into the refersto box and then press OK. If I press Add, it adds the sheet names and then pressing OK gives me an error about the formula. However, pressing Close after the error seems to leave the name entered. I think the limit for a name reference is supposed to be 255 characters or thereabouts but obviously there are some workarounds to get longer formulas in there, albeit with side effects!
Regards,
Rory
Microsoft MVP  Excel