| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Suggestion needed
If your worksheets are named Project1, Project2, Project3, Project4
and Project5... If your Resource names are in column A in all spreadsheets... If Capital/Expense is in column B in all spreadsheets... If the % is in column C in all spreadsheets... If the Resource Names are in column A on the Admin worksheet... If "John Doe" is in cell A2 Create a helper column in column B on the Admin tab. Set the formula in B2 to: =SUMIF(Project1!A:A,admin!A2,Project1!C:C)+SUMIF(P roject2!A:A,admin! A2,Project2!C:C)+SUMIF(Project3!A:A,admin!A2,Proje ct3!C:C) +SUMIF(Project4!A:A,admin!A2,Project4!C:C)+SUMIF(P roject5!A:A,admin! A2,Project5!C:C) Then, in column C, you will place the formula that will give you the amount of admin time: =IF(B2=1,0,1-B2) Be prepared to replace the "1" in both places in this formula with "100" depending on how you've formatted your % numbers. Now copy the formula in B2 down the B column, and the formula in C2 down the C column. If desired, hide column B on the Admin tab, as it is a helper column and might be confusing. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Suggestion needed
That is an AWESOME idea!!! thank you so much!
" wrote: If your worksheets are named Project1, Project2, Project3, Project4 and Project5... If your Resource names are in column A in all spreadsheets... If Capital/Expense is in column B in all spreadsheets... If the % is in column C in all spreadsheets... If the Resource Names are in column A on the Admin worksheet... If "John Doe" is in cell A2 Create a helper column in column B on the Admin tab. Set the formula in B2 to: =SUMIF(Project1!A:A,admin!A2,Project1!C:C)+SUMIF(P roject2!A:A,admin! A2,Project2!C:C)+SUMIF(Project3!A:A,admin!A2,Proje ct3!C:C) +SUMIF(Project4!A:A,admin!A2,Project4!C:C)+SUMIF(P roject5!A:A,admin! A2,Project5!C:C) Then, in column C, you will place the formula that will give you the amount of admin time: =IF(B2=1,0,1-B2) Be prepared to replace the "1" in both places in this formula with "100" depending on how you've formatted your % numbers. Now copy the formula in B2 down the B column, and the formula in C2 down the C column. If desired, hide column B on the Admin tab, as it is a helper column and might be confusing. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HELP! - Formula suggestion | Excel Discussion (Misc queries) | |||
| How to see New Suggestion | Excel Discussion (Misc queries) | |||
| I need a suggestion on... | New Users to Excel | |||
| Suggestion | Excel Worksheet Functions | |||
| Suggestion on this one? | Excel Discussion (Misc queries) | |||
Hybrid Mode
