Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula Troubles........
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 3D SUM it is in the cell for each month of each person listed on the Avaiable tab: =Master!B4-SUM(Data:Absence!B5) This allows me to insert or delete tabs/sheets between Data & Absence and the formulas adjust accordingly. .. For the name (column) on each tab/sheet I have a formula of: =Master!A5 This is the cell of each person's name it corresponds to the location of that person on the Master tab/sheet (each tab/sheet is formatted exactly the same so it is the same cell location on each tab/sheet as the Master i.e it is A5 on the Master it is A5 on all the others). .. What I need to do is anytime a row is inserted, deleted on the Master tab have it automatically update the Avaiable and Project tabs. I select (or group) all the tabs/sheets together so that it will carry over to all, then when I add a new row (or name) on the Master tab/sheet it does shift everything down on the other tabs/sheets and the person who A9 before insert has shifted to A10 and the formula's update but the new row I have added in, the name cell or any of the month cells have no formula's and if I go to type the fomula in the namce cell I get a circular reference error. I would like to not have to manually do any fomual updates. Is there a way I can have this scenerio work? Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |