# Persistent Formula / Graph

1. ## 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. 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)

3. 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?)

4. Just leave the sheets blank.

5. 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. 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. i was able to get it working, thank you for the assistance.

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

#### 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