Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  4. #4
    4 Star Lounger
    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",(10-G7)*H7,(10-G7)*H7*0.05),0)+
    IF(J7="Y",IF(M7="Y",(10-K7)*L7,(10-K7)*L7*0.05),0)+
    IF(N7="Y",IF(Q7="Y",(10-O7)*P7,(10-O7)*P7*0.05),0)+
    IF(R7="Y",IF(U7="Y",(10-S7)*T7,(10-S7)*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
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  6. #6
    4 Star Lounger
    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

Posting Permissions

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