+ Reply to Thread
Results 1 to 9 of 9

Issue with Formula

  1. #1
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66

    Issue with Formula

    Hi all,

    Just wondering if someone can help me with the following issue:

    I have one workbook which has 50+ worksheets - one of the worksheets is a recap of all 50+; therefore in one columns I'm summing up the total for say Revenue, now the formula I have is the following:

    Please Login or Register  to view this content.
    Now the above formula continues to include all 50+ worksheets; however, when I attempt to input the above into a cell on the recap worksheet Excel keeps telling me that the formula is too long! Can someone please advise on what I can do to resolve the above issue.

    Many thanks.

  2. #2
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    762

    3d formula

    HI
    try
    =sum('Campaign 1 - blahblah'!C11:'Campaign 50 - blahblah'!C11)
    or paste the following codes in the macro window(Alt F11)

    Please Login or Register  to view this content.
    Run the macro. it will give total in cell A1.
    Ravi

  3. #3
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    if it is allways the same cell and the sheets are grouped together then

    =SUM(Sheet1:Sheet3!A1)

    will sum cell a1 in sheet1, all the sheets after sheet1 and before sheet3 and sheet3

    Does that help?

    Regards

    Dav

  4. #4
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66
    Hi Rav,

    Just to confirm would the following be correct:

    Please Login or Register  to view this content.
    Thanks

  5. #5
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    762
    Hi
    YOur modification is not correct. It is a loop where value of "a" changes from 1 to 50 (or whatever the no of sheets you have). cell A1 keeps on accumulating (or totalling) C11 with every cycle adding value from one sheet. Use the original code
    Ravi

  6. #6
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66
    Hi,

    Okay, thanks for that.

    In relation to the the cell range (recap worksheet) that I want to populate it goes from cells C4:C34 Therefore would a modification of the macro be required to pre-populate those cells?

    Thanks

  7. #7
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    762
    HI
    try this modified macro
    Please Login or Register  to view this content.
    Ravi

  8. #8
    Registered User
    Join Date
    03-20-2007
    MS-Off Ver
    2007
    Posts
    66
    Hi,

    Thanks for that; however, I have a syntax error on the following line:

    Please Login or Register  to view this content.
    Any thoughts?

  9. #9
    Registered User
    Join Date
    03-12-2008
    Posts
    19

    quatations

    youve just missed " mark after the second cell value

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1