+ Reply to Thread
Results 1 to 7 of 7

Persistent Formula / Graph

  1. #1
    Registered User
    Join Date
    11-14-2008
    Location
    Mobile
    Posts
    4

    Persistent Formula / Graph

    Okay, so I have been redesigning our time sheet for a program at work. I am brand new to excel, having used the old works spreadsheet program for years.

    At the moment, I have all of the time sheets (weekly) in a workbook, and I have one sheet at the end that adds up all of hours worked into separate categories and totals them.

    Where I have gotten stuck is how to write a formula so that I won't have to edit it each and every time I insert a new time sheet.

    this is what it looks like at the moment:
    =SUM("sheet"!"cell containing hours" + etc..)
    -----
    ex.
    =SUM('Sept.29-Oct.5'!D22+'Oct.6-Oct.12'!D22+'Oct.13-Oct.19'!D22+'Oct.20-Oct.25'!D22+'Oct.26-Nov.1'!D22+'Nov.2-Nov.8'!D22+'Nov.9-Nov.15'!D22)
    -----

    Every time I add a new week into it, I have to reedit the formula. The format of the sheet remains the same every week, is there a way to allow for sheets to be added in automatically?

    Ideally, I would love to have a small, persistent graph/box that has all of the hourly totals in it, that would float on top of the workbook, separate from the workbook itself instead of having a sheet at the end.

    I don't know any of this is possible, but thank you for any help!

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Insert two 'bookend' worksheets at the left and right of the worksheets to be summed called, say, Left and Right, then change the formula to

    =SUM(Left:Right!D22)
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    11-14-2008
    Location
    Mobile
    Posts
    4
    keep it simple, eh? kicking myself for not thinking of that. should work perfectly, thanks!

    is there a way to exclude the two bookends from the calculations? (ex. if "left" and "right" don't have a numerical value at d22, or should I just assign it a '0' value?)
    Last edited by haysmj; 11-14-2008 at 03:42 PM.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Just leave the sheets blank.

  5. #5
    Registered User
    Join Date
    11-14-2008
    Location
    Mobile
    Posts
    4
    all it's giving me is a VALUE error now. is there something else I would have to adjust after inserting the bookend sheets?

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Is there a #VALUE error on one of the sheets?

    Use the Evaluate button on the Formula Auditing toolbar and watch the formula evaluate.

  7. #7
    Registered User
    Join Date
    11-14-2008
    Location
    Mobile
    Posts
    4
    i was able to get it working, thank you for the assistance.

+ 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