Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    Glen Innes, New South Wales, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula too long (Excel 97 on XP Pro)

    Have run into a problem using excel - it is telling me the formula I am using is too long and will not allow additional data to be entered. The excel file is a payroll where worksheets are used to calculate the pay. That information is then transferred to the Master worksheet (gross summary) then certain fields within the worksheets are selected as part of the formula on the master. All worked fine until now when trying to add infomation to the formula due to additional worksheets being added ... the comment formula too long will not allow additional data and therefore unable to get the master sheet to balance.

    The formula being used is as follows - =Eyears!C16+Eyears!C19+Eyears!C22+ and picks up data from another 30+ employees. Any help / suggestions would be appreciated!
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula too long (Excel 97 on XP Pro)

    You could select cells C16, C19, C22 etc. on the Eyears sheet using Click and Ctrl+Click, then give the selection a name using Insert | Name | Define...
    Say that you name the selection Employees. You can then use the formula =SUM(Employees)

  3. #3
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    Glen Innes, New South Wales, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula too long (Excel 97 on XP Pro)

    Thank you for your response while I can partly understand how this works I have run into the problem of how the formula is to progress with adding those same fields for each additional employee. Given that I guess the Name chosen must be unique so as to be able to progress the total on each additional employee.
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula too long (Excel 97 on XP Pro)

    You could create "partial sums" of cells which do not have formulas that are too long. The have a final formula add up all the partial ones...

    Some other options (II am not sure of how your sheets are setup so other things may work better), but perhaps a sumit formula could work or a pivot table.

    Perhaps a "redesign" may be needed.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    Glen Innes, New South Wales, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula too long (Excel 97 on XP Pro)

    Did as you suggested with the partial sums and all works well. Thanks
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula too long (Excel 97 on XP Pro)

    Hi Glen,

    From your post, it looks like you need to sum every 3rd row in the range. In that case, you could use:
    =SUM(IF(MOD(ROW(Eyears!$C$19:$C$100)-CELL("Row",Eyears!$C$19:$C$100),3)=0,Eyears!$C$19: $C$100,))
    as a standard formula to add every 3rd row from row 19 to row 100. Simply change the ranges to suit your needs, and the '3' to change the row frequency.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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