Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Auto Update upon shifting, inserting or deleting
I have a worksheet that is used for resource forecasting it has about 14 tabs
at the bottom. One of the tabs is a master that has all the names and a base amount of hrs that one would work per month i.e. John Smith Jan 120, Feb 120, Mar 138 and so on. The additional tabs are project tabs that has all the names (that are listed on the master tab) the person then adds the amount of time they will spend per month on a particular project i.e John Smith for Project A in Jan will spend 50 hrs. The formula that I have calculates all the hrs that the person enters on any of the project tabs against the Master tab (50 - 120) the results then display on a Avaiable tab (that also has all the names as the master) i.e. on the avaiable tab John Smith for Jan would show 70. My formula is a basic one it is in the cell for each person/month on the Avaiable tab: =Master!B4-SUM(DATA!B5,'712 '!B5,'COC Infra'!B5,'COC SLA'!B5,'FI Hub'!B5,SONIC!B5,M2A!B5,FTP!B5,Radiance!B5,'Windch ill COP'!B5,'On LOAN'!B5,Absence!B5) .. What I need to do is anytime a row is inserted, deleted or moved on the Master tab have it automatically update the Avaiable and Project tabs or if a project tab is deleted have it Automatically update the formulas to remove that reference |
#2
|
|||
|
|||
Tracy -
The last part of your question is easily handled by using a 3D SUM, changing your formula to =Master!B4-SUM(Data:Absence!B5) You can freely insert or delete sheets between Data & Absence and this formula will adjust accordingly. The other question - if all sheets are set up IDENTICALLY, then you simply need to select them all before modifying the Master sheet. Any changes you make to the Master while the sheets are grouped will propagate through to all selected sheets. AS SOON AS YOU FINISH MAKING CHANGES, UNGROUP YOUR SHEETS. Otherwise you will commit a common mistake - Thinking you are changing one sheet when you'rea ctually changing them all. If the other sheets are not set up identically, you'll need to get advice from somebody more experienced than me Duke "Tracy B" wrote: I have a worksheet that is used for resource forecasting it has about 14 tabs at the bottom. One of the tabs is a master that has all the names and a base amount of hrs that one would work per month i.e. John Smith Jan 120, Feb 120, Mar 138 and so on. The additional tabs are project tabs that has all the names (that are listed on the master tab) the person then adds the amount of time they will spend per month on a particular project i.e John Smith for Project A in Jan will spend 50 hrs. The formula that I have calculates all the hrs that the person enters on any of the project tabs against the Master tab (50 - 120) the results then display on a Avaiable tab (that also has all the names as the master) i.e. on the avaiable tab John Smith for Jan would show 70. My formula is a basic one it is in the cell for each person/month on the Avaiable tab: =Master!B4-SUM(DATA!B5,'712 '!B5,'COC Infra'!B5,'COC SLA'!B5,'FI Hub'!B5,SONIC!B5,M2A!B5,FTP!B5,Radiance!B5,'Windch ill COP'!B5,'On LOAN'!B5,Absence!B5) . What I need to do is anytime a row is inserted, deleted or moved on the Master tab have it automatically update the Avaiable and Project tabs or if a project tab is deleted have it Automatically update the formulas to remove that reference |
#3
|
|||
|
|||
Duke,
Do I put that formula in the define name under the insert menu? or do I type it in on the Avaiability tab where my formulas are stored now? I must be doing something wrong I tried to put it in the Name (the 3-D reference) but I can not get the results to display in the cell on the avaiability tab. "Duke Carey" wrote: Tracy - The last part of your question is easily handled by using a 3D SUM, changing your formula to =Master!B4-SUM(Data:Absence!B5) You can freely insert or delete sheets between Data & Absence and this formula will adjust accordingly. The other question - if all sheets are set up IDENTICALLY, then you simply need to select them all before modifying the Master sheet. Any changes you make to the Master while the sheets are grouped will propagate through to all selected sheets. AS SOON AS YOU FINISH MAKING CHANGES, UNGROUP YOUR SHEETS. Otherwise you will commit a common mistake - Thinking you are changing one sheet when you'rea ctually changing them all. If the other sheets are not set up identically, you'll need to get advice from somebody more experienced than me Duke "Tracy B" wrote: I have a worksheet that is used for resource forecasting it has about 14 tabs at the bottom. One of the tabs is a master that has all the names and a base amount of hrs that one would work per month i.e. John Smith Jan 120, Feb 120, Mar 138 and so on. The additional tabs are project tabs that has all the names (that are listed on the master tab) the person then adds the amount of time they will spend per month on a particular project i.e John Smith for Project A in Jan will spend 50 hrs. The formula that I have calculates all the hrs that the person enters on any of the project tabs against the Master tab (50 - 120) the results then display on a Avaiable tab (that also has all the names as the master) i.e. on the avaiable tab John Smith for Jan would show 70. My formula is a basic one it is in the cell for each person/month on the Avaiable tab: =Master!B4-SUM(DATA!B5,'712 '!B5,'COC Infra'!B5,'COC SLA'!B5,'FI Hub'!B5,SONIC!B5,M2A!B5,FTP!B5,Radiance!B5,'Windch ill COP'!B5,'On LOAN'!B5,Absence!B5) . What I need to do is anytime a row is inserted, deleted or moved on the Master tab have it automatically update the Avaiable and Project tabs or if a project tab is deleted have it Automatically update the formulas to remove that reference |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Update / Save | Excel Discussion (Misc queries) | |||
Auto update entire columns / rows??? | Excel Worksheet Functions | |||
excel links update not working in auto, calculations in auto | Excel Worksheet Functions | |||
auto update an open excel sheet over network, with mutiple users | Excel Worksheet Functions | |||
auto update | Excel Worksheet Functions |