Adding Worksheet

L

Lisa

I have a job project spreadsheet that has worksheets for days 1-4 and a
worksheet for the totals of all days. I need to add a day 5 worksheet, once
I add the worksheet how can I make the totals worksheet realize I added
another day and automatically pick up that info without having to go in and
redo the formulas on the totals sheet. I hope this makes sense.

Lisa
 
J

Jim Thomlinson

There is a bit of a trick to doing that. I assume that your day sheets are
all placed together in the workbook (they need to be for this to work). Add a
sheet just before and a sheet just after your day sheets (lets call them
Start and End). Now add a formula to sum (or otherwise aggregate) all of the
cells across the sheets including the blank sheet at the beginning and the
end of the days sheets. To do this in your totals sheet type "=Sum(" and then
select all of the sheets from Start to End by holding down the shift key and
then add the closing bracket. The formula will look like this...

=SUM(Start:End!A1)

Now just hide the sheets start and end. Now if you add another days sheet
(next to any of your existing days sheets it will be between the hiddend
sheets start and end and will therefore be calculated... I am not sure that
is the greatest expanation so if you need more assistance let me know...
 
L

Lisa

That worked perfectly for what I needed but now the boss is telling me he
wants it to do something different. He doesn't want me to put in a totals
sheet what he wants is on the original worksheet there is a space for total
project hours, he wants the day two sheet to have the hours from day one and
two in the total project hours and each consecutive day thereafter to add in
the previous days totals.

Ex: On day one we have a cell for total hours for the day and total hours
for the project. On day two we have a cell for total daily hours and total
project hours. I want the project total to reflect that day plus the
previous day. If the sheet is originally set up for a 4 day project but runs
5 days when an employee adds the sheet for day 5 I want it to automatically
pick up the formula that will keep adding the project total for the current
day to the project total for the previous day.

I think I have confused myself now.
 
J

Jim Thomlinson

For that you can use the Indirect function. You will have to add in the name
of the previous sheet somewhere on the newly created sheet. You can hide this
row so that no one else sees it... It's not too bad to do...
 
Top