Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Worksheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Worksheet
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... -- HTH... Jim Thomlinson "Lisa" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Worksheet
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. "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Lisa" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Worksheet
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... -- HTH... Jim Thomlinson "Lisa" wrote: 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. "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Lisa" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a "CLEAR" button to the worksheet | Excel Discussion (Misc queries) | |||
list worksheet macros/VBA without adding worksheet | Excel Discussion (Misc queries) | |||
Adding a title to a worksheet. How/Where? | New Users to Excel | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |